Editing Data Connections in Excel 2003

Hi,

I have a workbook with multiple worksheets and over 25 Data Connections using MS Query to connect to different tables in an SQL Server database.

The problem is that the SQL Server database is now being moved to a different server. The database name and the schema of the tables will be unchanged. Only the server name needs to be changed from XXXXX to YYYYY.

I have been unable to find a way to successfully edit the data connections in Excel 2003. A web search produced one potential option. However, that did not work for me very well. Thought the code is designed to cycle through all tablequeries and pivotqueries, the change is only made to one of the data connections. Here is the option I tried:

http://support.microsoft.com/kb/816562

Note that it is very easy to edit data connections in Excel 2007. So I also tried opening the .xls file in Excel 2007. I used the Data Connections button under the Data tab in the ribbon and was successful in editing the connections to point to the new server. However, the graphs used to display the queried data behave weird. The "format axis" settings and chart sizes change. In general, default display settings for charts seems to differ between Excel 2003 and 2007. I used Excel 2007 to only edit the data connections and saved the workbook in the original .xls format.

Does anyone have any ideas on editing server settings within data connections in Excel 2003. I am frustrated with the compatibility issues between 2003 and 2007. Excel 2007 makes editing data connections a snap, but messes up the display of the charts.

Any input is much appreciated.

What happens when you step through the code, line by line.
Does it not find multiple queries over multiple sheets?