Excel Automation, Sync all DataSources Synchronously

Another day doing some Excel Automation i needed to Refresh All Workbook connections, but do it synchronously, and avoiding the nasty error “Call was rejected by Callee”.

As far as i know this error appens becouse when you do a RefreshAll() on the book some connections refresh assynchronously and when you try to access a worksheet it will launch that error.

The solution for my case was foreach all the workbook connections change the Background query property to false and doing the Refresh.

Code:

foreach (Excel.WorkbookConnection conn in book.Connections)
{
    if (conn.Type == Microsoft.Office.Interop.Excel.XlConnectionType.xlConnectionTypeOLEDB)
    {       
        if (!conn.OLEDBConnection.OLAP)
            conn.OLEDBConnection.BackgroundQuery = false;
    }
    else if (conn.Type == Microsoft.Office.Interop.Excel.XlConnectionType.xlConnectionTypeODBC)
    {
        conn.ODBCConnection.BackgroundQuery = false;
    }

    conn.Refresh();
}

excelApp.CalculateUntilAsyncQueriesDone();

Notes

  • Checking BackgroundQuery on OLAP gives me a ArgumentException, but in my testings the refresh is done synchronously even without changing this property.
  • Reading the MSDN documentation it should be sufficient to execute the “CalculateUntilAsyncQueriesDone();” after the RefreshAll but it didnt work for me… But i still call this method on the end.

Hope it helps.

Advertisements

One thought on “Excel Automation, Sync all DataSources Synchronously

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s