Changing SQL server IP in Excel file?

An Excel file uses an SQL Server table to which it connects using MS
Query via ODBC. with the data being refreshed when needed with
Data/Refresh Data. The SQL server is moved to a different IP address
(say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
2000 can no longer find the table (obviously). How can one correct the
file to use the new SQLS address?
I have changed the SQLS entry in the ODBC Data Source Administrator --
this makes no difference, as the address is embedded in the XLS file,
and not picked up from the local machine's ODBC settings. If I choose
"Data/Edit Data" Excel tries to access the table and gives up with "SQL
Server does not exist". (And even with a valid SQL server address, I
couldn't find anywhere in the Query's options or SQL to change the SQL
server address).
I got round the problem with a binary file editor, replacing all 4
occurrences of "192.168.1.1" in the table with the new address (which
has the same number of characters).
But what is the proper way to do this, please?
Best wishes,
--
Michael SalemHi Michael,
I ran into a similar problem when I tried to use an excel sheet that was
developed on one server onto another. The only difference is that I am using
Excel 2003. When I tried to refresh the data, the system came up with an
error about invalid odbc connection. After acknowledging the error, it asks
to select another ODBC connection which I did and the refresh could then
complete properly. What exactly do you get when you initially refresh the
table?
Regards
"Michael Salem" <msnews@.ms3.org.uk> wrote in message
news:MPG.1c76f6f7aa5404cb989697@.msnews.microsoft.com...
> An Excel file uses an SQL Server table to which it connects using MS
> Query via ODBC. with the data being refreshed when needed with
> Data/Refresh Data. The SQL server is moved to a different IP address
> (say, original address = 192.168.1.1, new address = 192.168.1.9). Excel
> 2000 can no longer find the table (obviously). How can one correct the
> file to use the new SQLS address?
> I have changed the SQLS entry in the ODBC Data Source Administrator --
> this makes no difference, as the address is embedded in the XLS file,
> and not picked up from the local machine's ODBC settings. If I choose
> "Data/Edit Data" Excel tries to access the table and gives up with "SQL
> Server does not exist". (And even with a valid SQL server address, I
> couldn't find anywhere in the Query's options or SQL to change the SQL
> server address).
> I got round the problem with a binary file editor, replacing all 4
> occurrences of "192.168.1.1" in the table with the new address (which
> has the same number of characters).
> But what is the proper way to do this, please?
> Best wishes,
> --
> Michael Salem|||I wrote:

Many thanks to imad koussa, who responded:
[vbcol=seagreen]
> I ran into a similar problem when I tried to use an excel sheet that was
> developed on one server onto another. The only difference is that I am usi
ng
> Excel 2003. When I tried to refresh the data, the system came up with an
> error about invalid odbc connection. After acknowledging the error, it ask
s
> to select another ODBC connection which I did and the refresh could then
> complete properly. What exactly do you get when you initially refresh the
> table?
After waiting for 64 seconds (a long time) I am asked to log in again. I
type in the correct address and the table is refreshed in 6 seconds.
But nothing I have been able to do stores the correct address in the
.XLS file. As it is used frequently by many users, I need to correct the
file, rather than wait a full minute and type in a numeric address.
This is not a major problem, but I would have expected to find a better
way to store the corrected information than editing the .XLS file with a
binary editor.
Best wishes,
--
Michael Salem