Changing "External Data" DSN Details to point to different server

[KD]

I've a number of spreadsheets that retrieve external data
from a SQL Server database. (These were written using the
wizard invoked by Data > Get External Data > New Database
Query ).

The database has been moved to a new server (which has a
different name). The DSN for the PC has been changed to
point to the correct location (Control Panel >
Administrative Tools > Data Sources) but the spreadsheet
still looks in the old location.

If the Microsoft Query had been saved (as a dqy file)
before returning the data to excel, I could modify this
with Notepad to correct the DSN it's using. However, for
a lot of these spreadsheets, there is no dqy file. How do
I change the DSN when it is embedded in the spreadsheet?


Thanks


KD.

 

[Jamie Collins]

...

I've a number of spreadsheets that retrieve external data
from a SQL Server database. (These were written using the
wizard invoked by Data > Get External Data > New Database
Query ).

The database has been moved to a new server (which has a
different name). The DSN for the PC has been changed to
point to the correct location (Control Panel >
Administrative Tools > Data Sources) but the spreadsheet
still looks in the old location.

If the Microsoft Query had been saved (as a dqy file)
before returning the data to excel, I could modify this
with Notepad to correct the DSN it's using. However, for
a lot of these spreadsheets, there is no dqy file. How do
I change the DSN when it is embedded in the spreadsheet?

This may help:

http://www.dicks-clicks.com/excel/ExternalData5.htm#ChangeConn

Jamie.

--

 

[KD]

Terrific, thanks Jamie.

-----Original Message-----
...


This may help:

http://www.dicks- clicks.com/excel/ExternalData5.htm#ChangeConn

Jamie.