Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change Excel "External Data" Connection String

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).

like image 571
Doctor Jones Avatar asked Sep 01 '11 09:09

Doctor Jones


People also ask

How do I change the connection string in Excel?

The Workbook Connections dialog box (Select Data > Connections) helps you manage one or more connections to external data sources in your workbook. You can use this dialog box to do the following: Create, edit, refresh, and delete connections that are in use in the workbook.

How do I change the external data source in Excel?

In Excel, on the Data tab, in the Get External Data section, click From Other Sources, and then select your data source.

How do I change the query connection in Excel?

In Excel, select Data > Queries & Connections, and then select the Queries tab. In the list of queries, locate the query, right click the query, and then select Edit.

How do you change the name of the external data range?

Change the name of an external data rangeSelect Formulas > Name Manager. In the Name Manager dialog box, click the name of the external data range, and then click Edit. You can also double-click the name. In the Edit Name dialog box, type the new name for the reference in the Name box.


1 Answers

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;
}
like image 68
Doctor Jones Avatar answered Oct 12 '22 07:10

Doctor Jones