Re: Re-pointing Excel ODBC query to different system

kirkg@xxxxxxxxxxxxx wrote:
> I created an excel book that has 4 sheets, each sheet uses a ODBC MS
> Query to pull a subset of data from the same file. Now I'm done
> testing how can I get Excel to point to a Different System?
>
>  The only way I can see is via an new worksheet/data/get ext data/new
> db query which means I have to re-create the worksheet from scratch

My experience echoes yours, you can't point a query to another system - I
assume because the data source "hard points" to the original system.
Libraries can be changed though.

Here are some options:
If you know how, you can change the Connection Property of the QueryTable.
The DSN= is part of the connect string.  It's easy to do if you know how to
manipulate the spreadsheet objects in the VisualBasicApps language.

Select the SQL button on the Query (if you used the MSQuery interface to
define the query) and select and copy all of the statement.  Cancel back
and on a new sheet create an external data query.  Select the new data
source.  Cancel out of the table select.  Select the SQL button and paste
the statement you copied earlier.  Change the system name portion of the
"From" statement(s) and library name if necessary.

Bill