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.

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.