Reputation: 428
I have a workbook (Excel 2016) with a few dozen "Connections" defined. Each connection has different "Command Text" but the exact same Connection String. Because this connection string will be changing often (monthly password changes or the occasional database move), I would like to share a single connection-string amongst many different Commands.
I saw a Microsoft article that said I could "Export Connection" as an .odc file, which I did--but I'm not used to Excel--where I come from "Connection" is one thing, "Command" is another--and this .odc file contains both. So when I set all of my Connections to use the single .odc "Connection" file, all of my "Commands" were identical, so that was a disaster.
I have to trust that there really is a way to share a single connection among many different commands that all come from the exact same database. (edit)--I really don't want a Macro or more code to plop in a connection string--I'm hoping for something very similar to the "Connection File" concept--but one that really is a "Connection" only. Thanks
Upvotes: 2
Views: 5755
Reputation: 21657
The short answer is Yes - you can re-use connections from Excel's Queries & Connections window, and are encouraged to do so for the same reasons you mentioned, and more.
How to do it in your individual case depends on a number of factors that aren't included in your question such as where the data is coming from and what it's being used for.
Hopefully some of these resources will help shed some light...
Super User : "Reuse SQL View in different sheets in SQL with different subqueries"
MS TechNet : "How can I re-use Power Queries?"
WiseOwl : How to reuse a query created in Query Editor or Power Query
Office.com : Overview of connecting (importing) data
Office.com : Connection properties (Excel)
Upvotes: 1