If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Switching ODBC sources in Excel 2007
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? |
#2
|
|||
|
|||
Switching ODBC sources in Excel 2007
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. "MichaelS" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|