Microsoft Office Interop Timing Out

Question

I am working with a C# program which utilises Microsoft Office Interop allowing you to essentially programatically use Microsoft Word, Excel and PowerPoint.

One unfortunate aspect I've encountered is that on occasion, the program will raise a dialogue box which pauses the code. This can happen for example if suddenly the code is not able to save in a location you expected to be able to, meaning your whole program is paused likely even if that issue is fixed later.

There are lots of other circumstances where an additional dialogue box might be raised.

So my intention is to implement some kind of timeout mechanism for this, whereby I can kill the Interop instance rather than have my whole program tied up. Can anyone suggest a way to do this? At the moment it wraps the Interop calls in a System.Action and aborts that thread after a given time, but I wonder if there's a better way.

I've done stuff like this in the past. The only thing to do is actually read the VBA code you are automating, and test it so you know when and where dialogue boxes appear, and why. Then you can program around it. Trust me it will make your life easier in the long run! — JMK, Jul 04 '13 at 10:56
Also, Office Interop isn't designed to be used unattended, the idea is that your end user sees those dialogue boxes and interacts with them appropriately. If you want unattended Office automation, you could look at OpenXML. — JMK, Jul 04 '13 at 10:59

score 1 · Answer 1 · edited May 23 '17 at 12:05

1

You can do this by implementing an OLE Message Filter. See this answer for more details.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 04 '13 at 12:26

Joe

122,218
32
205
338

score 1 · Answer 2 · edited May 23 '17 at 10:25

Many people don't recommend killing the process; See How to properly clean up Excel interop objects and Understanding Garbage Collection in .net

Here is the code I use to kill the Excel instance that I created. You will need to refactor it a bit to meet your needs. You'll see how to get the process ID using the window handle provided by Excel. I imagine the process would be the same for Word or Powerpoint.

'http://msdn.microsoft.com/en-us/library/ms633522%28v=vs.85%29.aspx
<System.Runtime.InteropServices.DllImport("user32.dll", SetLastError:=True)> _
    Private Shared Function GetWindowThreadProcessId(ByVal hWnd As IntPtr, _
    ByRef lpdwProcessId As Integer) As Integer
End Function

Sub Work()

    'declare process; will be used later to attach the Excel process
    Dim XLProc As Process

    'start the application using late binding
    Dim xlApp As Object = CreateObject("Excel.Application")

    'or use early binding
    'Dim xlApp As Microsoft.Office.Interop.Excel

    'get the window handle
    Dim xlHWND As Integer = xlApp.hwnd

    'this will have the process ID after call to GetWindowThreadProcessId
    Dim ProcIdXL As Integer = 0

    'get the process ID
    GetWindowThreadProcessId(xlHWND, ProcIdXL)

    'get the process
    XLProc = Process.GetProcessById(ProcIdXL)


    'do some work with Excel here using xlApp

    'be sure to save and close all workbooks when done

    'release all objects used (except xlApp) using NAR(x)


    'Quit Excel 
    xlApp.quit()

    'Release
    NAR(xlApp)

    'Do garbage collection to release the COM pointers
    'http://support.microsoft.com/kb/317109
    GC.Collect()
    GC.WaitForPendingFinalizers()

    'I prefer to have two parachutes when dealing with the Excel process
    'this is the last answer if garbage collection were to fail
    If Not XLProc Is Nothing AndAlso Not XLProc.HasExited Then
        XLProc.Kill()
    End If
End Sub

Private Sub NAR(ByVal o As Object)
    'http://support.microsoft.com/kb/317109
    Try
        While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
        End While
    Catch
    Finally
        o = Nothing
    End Try
End Sub

Microsoft Office Interop Timing Out

2 Answers2