We have a workbook that contains a number of sheets that all inter-relate. As
far as i can tell there are only two places which this workbook calls data to
fill in the sheets. These two places consist of two buttons that link to a
SQL server. Recently we had to move the server data files over to SQL 2005
and now no longer connect to the tables in order to run the spreadsheets. How
do I change the direction in the workbook to point to the new server?

It depends on how the buttons call the data. You need to investigate
the document and see if those buttons call Macros, and if so then you
can modify the Macro to point to the new SQL path

Well, when I press the buttons they open asking to connect to the database
and then request the username and password. But I can't seem to find in the
code where you could hard code those responces. The box that pops up has the
old database in it.

There are a number of macros that run but even in them I do not see the ODBC
connection string.

"Matt" wrote:

It depends on how the buttons call the data. You need to investigate
the document and see if those buttons call Macros, and if so then you
can modify the Macro to point to the new SQL path

I just solved this for myself.

Open the worksheet and place the cursor on a cell within the cell range of
the query. Press Alt-F11 to open the VBEditor. Press Ctrl-G to open the
Intermediate window. Type the command: ? ActiveCell.QueryTable.Connection.
The embedded connection string will be echoed back to the screen. Put double
quotes around the string and update the connection information with the new
server info. Move the cursor to the beginning of the connection string and
insert the folling in front of the string:

ActiveCell.QueryTable.Connection =

Move the cursor to the end of the string and press enter. This will store
the updated connection string back into the worksheet. Try your query now.

Repeat for any other embedded queries in your worksheet.

"Cameron" wrote:

Well, when I press the buttons they open asking to connect to the database
and then request the username and password. But I can't seem to find in the
code where you could hard code those responces. The box that pops up has the
old database in it.

There are a number of macros that run but even in them I do not see the ODBC
connection string.

"Matt" wrote:

It depends on how the buttons call the data. You need to investigate
the document and see if those buttons call Macros, and if so then you
can modify the Macro to point to the new SQL path