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

Excel Automation Locale Issues

If you are doing Excel Automation, and are having errors like "Exception from HRESULT: 0x800A03EC" or "Old format or invalid type library" probably it’s a location problem.
 
One solution is to force a culture change when using excel objects, but there’s a better and simplified way, that only requires to instantiate the Excel.Application object like this:
 

Excel.Application excelApp = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), new Excel.Application());

With this all the comunication with excel via this object will be done in locale 1033, it basically creates a proxy that comunicate with Excel always in locale 1033 like in VBA.

For more detail check:

http://blogs.msdn.com/eric_carter/archive/2005/06/15/429515.aspx

http://blogs.msdn.com/eric_carter/archive/2005/08/25/453680.aspx