tim laqua dot com Thoughts and Code from Tim Laqua

Changing Server Names in Excel 2007 Embedded Workbook Connections

Ok, so you want to migrate servers, eh? Well - all your business users pry have a couple thousand Pivot Reports lying around that point to the old server. We need to point all references to the old server at the new server.

There are two places these references exist (afaik) - The My Data Sources folder (Windows XP / Excel 2007) which holds all of the odc objects for the logged in user, and the real pain - Embedded data sources in Excel Workbooks.

For the odc objects - you can just loop through the My Data Sources folder files and do a little find/replace action.

For the Excel files - we need to first find all the files, then loop through them and hunt for embedded Connections. Once we find a connection, we can just blindly replace occurrences of the old server name with the new server name. The first thing we need here is a function we can call recursively to go hunt down all the excel files - then we loop through and play with the connections (when found).