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

 

Get SMTP email from Exchange Account in OutLook VSTO

Yesterday, I needed to obtain the primary SMTP email account from a MailItem in a Outlook VSTO AddIn…

 

So as usual I jump into Google and start searching (learning) how to do it, and all that I found was scary and non supported solutions like using third party dll’s like Redemption or code using ExtendedMAPI…

 

But I found another way that worked perfectly well for me and it is much more simple.

 

Here’s my solution:

 

  public static List<string> GetDestinations(Outlook.MailItem mail)

        {

            try

            {

                List<string> mailAddressList = new List<string>();

               

                foreach (Recipient recipient in mail.Recipients)

                {

                    if (recipient.AddressEntry.Type == "EX")

                        mailAddressList.Add(recipient.AddressEntry.GetExchangeUser().PrimarySmtpAddress);

                    else

                        mailAddressList.Add(recipient.Address); 

                }

 

                return mailAddressList;

            }

            catch (System.Exception ex)

            {

                Logger.Error(ex, "GetDestinations");

                throw;

            }

        }

 

This method returns all the destinations of a MailItem, and check’s if it is an exchange account an uses the Outlook API method “GetExchangeUser()” to obtain the PrimarySmtpAddress of the destination.

 

Hope it helps, for me it worked perfectly so far.