Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Data Sources on Z (depth) axis in Excel 2007 | Charts and Charting in Excel | |||
Pivottables - Can i consolidate 2 Data sources in Excel 2007? | Excel Discussion (Misc queries) | |||
ODBC Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 and External Data Sources | Excel Discussion (Misc queries) | |||
Pivot tables, external data sources and ODBC links | Excel Worksheet Functions |