Changing the External Data Connection String

Question

We are currently using pivot tables in Excel 2003 to run our reports. These pivot tables use Excel's built-in Import External Data features to report from SQL (for example, SQL Server 2008).

The reports currently point to our UK database, but now we would like to make a copy of each report pointing to our new US database (which has the same layout as the UK database).

Instead of painstakingly going through almost 100 spreadsheets, I was hoping there would be a good COM automation bit that I could use to change the connection strings in each of the spreadsheets.

Does anyone know how to change the connection string of an external data source from COM?

I use .Net (in particular, C #), but I would be grateful for any help, regardless of language or method (this should not be COM).

Answer 1

After looking at various VBA examples and the MSDN COM documentation, I figured out how to do this.

The important part is that the connection strings are stored in one of two places, depending on how you created your worksheet.

• If you used the pivot table wizard, then the connection strings will be saved in the collection returned by the Workbook.PivotCaches() function (PivotCache return objects have a Connection property that contains the connection string).

• If you used "Import External Data", the connection strings will be stored in the collection returned by the Worksheet.QueryTables property (QueryTable return objects have a Connection property that contains the connection string).

There may be more places that can be saved in Connection strings, these are the only ones that I know of so far. If you know more, leave any information in the comments and I will add an answer.

Here's a well-commented full C # working example to help someone else running into this problem:

 static void ChangeConnectionStrings(string directoryName, string oldServerName, string newServerName) { var directory = new DirectoryInfo(directoryName); //get all the excel files from the directory var files = directory.GetFiles("*.xls", SearchOption.AllDirectories); Microsoft.Office.Interop.Excel.Application application = null; try { //create a new application application = new Microsoft.Office.Interop.Excel.Application(); //go through each excel file foreach (var file in files) { //open the file application.Workbooks.Open(file.FullName); //get the query tables from the worksheets var sheets = application.Sheets.OfType<Worksheet>(); var queryTables = sheets.SelectMany(s => GetQueryTables(s)); //change the connection string for any query tables foreach (var queryTable in queryTables) { queryTable.Connection = queryTable.Connection.Replace(oldServerName, newServerName); } //get the pivot table data from the workbooks var workbooks = application.Workbooks.Cast<Workbook>(); var pivotCaches = workbooks.SelectMany(w => GetPivotCaches(w)); //change the connection string for any pivot tables foreach (var pivotCache in pivotCaches) { pivotCache.Connection = pivotCache.Connection.Replace(oldServerName, newServerName); } Console.WriteLine("Saving " + file.Name); //save the changes foreach (var workbook in workbooks) { workbook.Save(); workbook.Close(); } } } finally { //make sure we quit the application if (application != null) application.Quit(); } } //PivotCaches isn't Enumerable so we can't just use Cast<PivotCache>, therefore we need a helper function static IEnumerable<PivotCache> GetPivotCaches(Workbook workbook) { foreach (PivotCache pivotCache in workbook.PivotCaches()) yield return pivotCache; } //QueryTables isn't Enumerable so we can't just use Cast<QueryTable>, therefore we need a helper function static IEnumerable<QueryTable> GetQueryTables(Worksheet worksheet) { foreach (QueryTable queryTable in worksheet.QueryTables) yield return queryTable; }