Writing Data From .Net to Excel Interrupted by User Opening Excel Window

Problem

I have an application that generates financial reports in excel. After installing Office 2013, if a user opens excel while the application is running, the application returns a error: System.Runtime.InteropServices.COMException with HRESULT: 0x800AC472 which interrupts the data from being written to the spreadsheet.

After some research according to Microsoft this was done by design, the error  0x800AC472 – VBA_E_IGNORE, is thrown because Excel is busy and will not service any Object Model calls… this is a link to the discussion

Solution

You need to catch the error and retry.  Not the most elegant solution, but it works. This code is VB .Net – if you need a C# version, please let me know and I will add it.

 Public Function SetText(ByRef oSheet As _Worksheet,
                myRange as string,
                pText As String,
                Optional ByVal fname As String = "Arial",
                Optional ByVal fsize As Int16 = 12,
                Optional ByVal bBold As Boolean = False,
                Optional ByVal bItalics As Boolean = False,
                Optional ByVal wrapText As Boolean = False ) As Boolean

Dim retry as Boolean = true

While retry

        Try
          With oSheet.Range(myRange)
               .Value = pText
               .Font.Name = fname
               .Font.Size = CLng(fsize)
               .Font.Bold = CBool(bBold)
               .Font.Italic = CBool(bItalics)
               .WrapText = wrapText
          end with 

          retry = False

       Catch ex As Exception
          retry = ex.Message.Contains("0x800AC472")
       End Try
     End While

     return true

  End Function

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