Changing the PivotCache connection of an Excel spreadsheet in C#

Question

I'm loading an excel spreadsheet with pivot tables and charts in it into a web-browser control using C#. The spreadsheets have several connection strings all pointing to a development database. I would like to be able to change the connection strings to one that has been supplied by the user when my application is run.

I have already managed to get this to work when the spreadsheet was created programmatically and the SourceType of the pivot caches are set to 'External'. However, when a spreadsheet that was created in Excel is loaded the source type is set to 'Database' and exceptions are thrown when the 'Connection' property is accessed.

Is there a way to change the SourceType (read-only) property or the connection string of such a spread sheet?

Here is a sample of my code which is based on a solution to a similar problem.

EXCEL.Worksheet sheet = (EXCEL.Worksheet)_application.ActiveSheet;
foreach (EXCEL.PivotTable table in sheet.PivotTables())
{
      table.PivotCache().Connection = ConnectionString;
      table.RefreshTable();
}

I have also tried this

var workBooks = _application.Workbooks.Cast<EXCEL.Workbook>();
var pivotCaches = workBooks.SelectMany(arg => GetPivotCaches(arg));

foreach (EXCEL.PivotCache cache in pivotCaches)
{
    cache.Connection = ConnectionString;
}

In both cases I get a System.Runtime.InteropServices.COMException as soon as I access the Connection property of the pivot cache. Any ideas?

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Here is the fix for the problem based on a solution which I found here.

        //update the connections
        foreach (EXCEL.WorkbookConnection connection in workbook.Connections)
        {
            if (connection.Type.ToString() == "xlConnectionTypeODBC")
            {
                connection.ODBCConnection.BackgroundQuery = false;
                connection.ODBCConnection.Connection = ConnectionString;
            }
            else
            {
                connection.OLEDBConnection.BackgroundQuery = false;
                connection.OLEDBConnection.Connection = ConnectionString;
            }
        }

        //Refresh all data
        workbook.RefreshAll();

This fixed the problem with most of the reports throwing exceptions. The only one that didn't work was re-created from scratch (it was really old and had been around the block!)

Hope this helps others.

Changing the PivotCache connection of an Excel spreadsheet in C#

1 Answers1