Changing ODBC
 

I have a large spreadsheet with an ODBC link into our main company database.
It then feeds several Pivot tables.

I now need to keep the spreadsheet but change which ODBC link the query is
using. How can I do this? So far apart from when you first create the query
I can't see any way to change it later.

Changing ODBC
 

I'd select the sheet with the link, jump into my 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
Hopefully the result makes enough sense that you can figure out what you
need it to be, so then just change it:
ActiveSheet.querytables(1).connection = xxxxxxxxxxxx
HTH. --Bruce

"Keith" wrote:

I have a large spreadsheet with an ODBC link into our main company database.
It then feeds several Pivot tables.

I now need to keep the spreadsheet but change which ODBC link the query is
using. How can I do this? So far apart from when you first create the query
I can't see any way to change it later.

Changing ODBC
 

Thanks for you help. That worked a treat.

Keith

"bpeltzer" wrote:

I'd select the sheet with the link, jump into my 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
Hopefully the result makes enough sense that you can figure out what you
need it to be, so then just change it:
ActiveSheet.querytables(1).connection = xxxxxxxxxxxx
HTH. --Bruce

"Keith" wrote:

I have a large spreadsheet with an ODBC link into our main company database.
It then feeds several Pivot tables.

I now need to keep the spreadsheet but change which ODBC link the query is
using. How can I do this? So far apart from when you first create the query
I can't see any way to change it later.