Change Excel "External Data" Connection String

Question

We currently use pivot tables in Excel 2003 to do our reporting. These pivot tables use the inbuilt "Import External Data" Excel functionality to feed the reports from SQL (SQL server 2008 to be precise).

The reports currently point at our UK database, but we'd now like to make a copy of each report that point at our new USA database (which has the same schema as the UK database).

Rather than painstakingly go through nearly 100 spreadsheets I was hoping that there would be a nice bit of COM automation that I could use to change the connection strings in each of the spreadsheets.

Does anyone know of a way to change the external data source connection string from COM?

I'm using .Net (specifically C#) but I'd be grateful for any help regardless of language or method (it doesn't have to be COM).

Answer 1

After looking at various VBA examples and the MSDN COM documentation I've figured out how to do it.

The important part is that Connection strings are kept in one of two places depending on how you created your worksheet.

1. If you've used the pivot table wizard then the connection strings will be stored in the collection returned by the Workbook.PivotCaches() function (the PivotCache objects returned have a Connection property which contains the connection string).

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

There may be more places that Connection strings can be stored, these are the only two that I'm aware of so far. If you know of any more please leave some information in the comments and I'll add to the answer.

Here's a nicely commented full working C# example to help anyone else that comes across 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;
}