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.
Rui Romano's Blog 