Excel stil trying to connect old DB server.

[Stingreen]

Guys,
We have a problem that all the tech department going crazy about. Basicly, We have 50 excel sheets which queries our SQL server and extracting
data out of it and creating graphs for us. The database IP used to be 90.0.0.110 but we decided to give database server a public ip address..
Now from the clients I deleted the "old" ODBC connection which goes to 90.0.0.110 and created the new one which is pointing to the public ip. The test goes thru without giving an error in odbc. However, when I try to update the data in the excel spreadsheet. It is "still" trying to refresh from our old database server IP address which is
90.0.0.110. I confirmed this by doing netstat -a and checking the active connections.
I checked registry settings, the settings in excel but no luck. It is "supposed" to be going the new ip address but it doesn't. Excel cached the old ip address or whatever it doesn't even check if it is changed or not. Is this a bug or what??
PS: Deleting the excel sheet and creating it solves it but it's not a solution for us because, the SQL code queries the database servers are pages long. It would take us years to save them all and restore it to the new excels..
Please advise,
Thank you.

 

[petrosky]

Hi,

One thing I would suggest checking is any predefined named ranges the workbook/s may have.

Do this by going to Insert, Name, Define.
Here you will see any Ranges that have been defined.
You may find for example, that the workbook/s are referencing a different directory....as below

='\\Server\company\Master\[example.xls]costing'!$A:$E

I hope this helps.

Peter
Remember- It's nice to be important,
but it's important to be nice