Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I change the datasource location for a query in Excel 2003
I am trying to recreate a report that went wrong for last month, and to do
so, I have to rename the original database so as not to corrupt it. This means I need to have the Excel template temporarily point to the renamed database so as to refresh the data using the results from the recovered database. There has to be an easier way than completely recreating the spreadsheet to get it to point to the new location of the Access data source. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I change the datasource location for a query in Excel 2003
For each sheet with a query you need to modify, select the sheet, jump into
the VB Editor (Alt + F11), then open up an immediate window (View / Immediate Window, if it's not already visible). To see what the current connection is, type the following line in the immediate window, followed by enter: Print ActiveSheet.querytables(1).connection You'll probably be able to figure out what you need it to be, so just change it: ActiveSheet.querytables(1).connection = xxxxxxxxxxxx If you've got multiple queries on a given sheet, repeat the print/update steps for each index value (querytables(2), etc). Repeat for each sheet with a query. "StormKatt" wrote: I am trying to recreate a report that went wrong for last month, and to do so, I have to rename the original database so as not to corrupt it. This means I need to have the Excel template temporarily point to the renamed database so as to refresh the data using the results from the recovered database. There has to be an easier way than completely recreating the spreadsheet to get it to point to the new location of the Access data source. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Excel 2000 - Change PivotTable DataSource (Access2000 database) | Excel Discussion (Misc queries) | |||
External data query leaves datasource workbook open | Excel Discussion (Misc queries) | |||
Change pivottable datasource | Excel Discussion (Misc queries) | |||
Excel 2003 workgroup template location | Excel Discussion (Misc queries) | |||
Chart DataSource Change | Charts and Charting in Excel |