Switching ODBC sources in Excel 2007

[MichaelS]

I use Excel 2003 extensively with ODBC and MS Query to retrieve data from an
external accounting program. Most of my worksheets have multiple external
data queries.

I recently upgraded to Excel 2007 and while I love many of the improvements
relating to external data connections, there is one thing I can't figure out.
With Excel 2003, you could use a macro to swap out or change the data
sources for multiple external queries all at once (see KB 816562). I'm
trying to determine if it's possible to do the same with Excel 2007. I can
individually edit the connection string for each external data connection to
point to a new data source but need to be able to swap them all at once.

Anyone have any ideas?

 

[bapeltzer]

I usually now modify ws.listobjects(1).querytable.connection rather than
ws.querytable(1).connection. You should be able to modify the 'for each'
loop in the kb article you referenced.