Opening Excel Causes Sporadic Hang

[nobbyv]

I've got a piece of code that creates a new Excel file from an existing file and renames it. It repeats this 100-200 times (the existing file is a log of USB data; the "new" file logs successive iterations of capturing data, FWIW).

The code works fine 99% of the time. But every once in awhile, it completely hangs when attempting to open the "new" Excel workbook (see line with double asterisks). The "new" file gets created and named properly ( I can open it manually, and get a "Read Only" notification), but it simply does not complete the process of opening the workbook. I have two Excel processes running when I open Task Manager (one for the new file, one for the existing), so that seems to make sense. The contents of ResultsFilePath exist and are correct, so no issue there.

But I can't figure out why it's hanging, or even what diagnostics I can wrap around it to capture it when it happens.

If it matters (though I don't see why it should), this is all happening in a BackgroundWorkerTask while my main task updates the UI.

Any help is appreciated.

    Function CopyFormatUSBDatatoReport(ByVal TouchDur As Integer, ByVal TestTime As Integer)       'Create report for each test from temp USB data location

    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    ResultsFilePath = ControllerSetup.ControllerTestResultFolderPath & TouchDur & "mS_TouchDuration_" & TestTime & "mS_TestTime.xlsx"
    My.Computer.FileSystem.CopyFile("C:\QA_Controller_Test_Files\USBCaptureTemp.xlsx", ResultsFilePath)          'Create log file for this touch duration

    oExcel = New Excel.Application                          'Create a new instance of Excel
    **oBook = oExcel.Workbooks.Open(ResultsFilePath)**          'Get correct file
    oSheet = oExcel.Worksheets(1)
    oExcel.Visible = False                                  'Don't show it to the user

'Do some other work

    oBook.Save()
    oBook.Close()                       'Disconnect from Excel and end processes
    oExcel.Quit()
    oBook = Nothing
    oExcel = Nothing
    GC.Collect()
    GC.WaitForPendingFinalizers()

[si_the_geek]

At the end of the code you set your objects to Nothing (as you should), but miss one out: oSheet

However, given that you don't actually do anything with that variable (other than assign a value to it, which you then ignore), I would recommend removing that variable entirely.

I would also recommend swapping the order of the lines oExcel.Quit() and oBook = Nothing

[TnTinMN]

Occasionally, interop COM objects do not de-reference quick enough to allow the Office application to shutdown. Your code shows signs of an attempt to deal with that but it is lacking completeness. Here is a example showing the basic pattern.

Private Sub ExcelBase()
	Dim app As New Excel.Application
	app.Visible = True

	' follow a single .Net variable assigned to an Excel object pattern
	' also referred to as the one dot rule.

	Dim books As Excel.Workbooks = app.Workbooks
	Dim wb As Excel.Workbook = books.Add 'Open("myWorkbook.xls")

	Dim sheets As Excel.Sheets = wb.Worksheets
	Dim ws As Excel.Worksheet = DirectCast(sheets.Item("Sheet1"), Excel.Worksheet)

	Dim rng As Excel.Range = ws.Range("A1:A10")
	Dim cells As Excel.Range = rng.Cells

	For Each cell As Excel.Range In cells
		' cell creates a Range Reference
		ReleaseCOM(cell)
	Next

	ReleaseCOM(rng)
	ReleaseCOM(ws)
	ReleaseCOM(sheets)
	wb.Close(SaveChanges:=False)
	ReleaseCOM(wb)
	app.Quit()
	' running the GC will free some COM reference that you missed explicity referencing/dereferencing
	ReleaseCOM(app, True)
End Sub

Friend Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional runGC As Boolean = False)
	Try
		If COMObj IsNot Nothing Then
			System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
		End If
	Finally
		COMObj = Nothing
		If runGC Then
			GC.Collect()
			GC.WaitForPendingFinalizers()
		End If
	End Try
End Sub

[nobbyv]

TnTinMN: I isolated this code down to just the copy utility and a few writes to the new Excel sheet, and implemented the suggestions you made. Unless I've missed something, however, it still isn't working: it hangs after 100-250 iterations. I don't *think* I need the explicit cell COM releases in my case, as I'm really only ever accessing a range. Please let me know if I'm wrong.

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        For X As Integer = 0 To 999999999
            CopyDatatoReport(X)
        Next

    End Sub

    Sub CopyDatatoReport(ByVal Iteration As Integer)

        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        Dim ResultsFilePath As String

        ResultsFilePath = "C:\Test\TestResults\Iteration" & Iteration & ".xlsx"
        My.Computer.FileSystem.CopyFile("C:\Test\Test.xlsx", ResultsFilePath)

        oExcel = New Excel.Application                          'Create a new instance of Excel
        oBook = oExcel.Workbooks.Open(ResultsFilePath)          'Get correct file
        oSheet = oExcel.Worksheets(1)
        oExcel.Visible = False                                  'Don't show it to the user

        Dim LastRow = oSheet.UsedRange.Rows.Count

        Dim oRange As Excel.Range
        oRange = oSheet.Range("B" & LastRow + 1)
        oRange.Value = "TEST1"
        ReleaseCOM(oRange)
        oRange = oSheet.Range("B" & LastRow + 2)
        oRange.Value = "TEST2"
        ReleaseCOM(oRange)
        oRange = oSheet.Range("B" & LastRow + 3)
        oRange.Value = "TEST3"
        ReleaseCOM(oRange)

        ReleaseCOM(oSheet)
        oBook.Save()
        oBook.Close()
        ReleaseCOM(oBook)
        oExcel.Quit()
        ReleaseCOM(oExcel, True)

    End Sub

    Friend Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional runGC As Boolean = False)
        Try
            If COMObj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
            End If
        Finally
            COMObj = Nothing
            If runGC Then
                GC.Collect()
                GC.WaitForPendingFinalizers()
            End If
        End Try
    End Sub

End Class

[TnTinMN]

You missed a few implicit COM object references. Hopefully this will resolve the issue.

Sub CopyDatatoReport(ByVal Iteration As Integer)

		Dim oExcel As Excel.Application
		Dim oBook As Excel.Workbook
		Dim oSheet As Excel.Worksheet

		Dim ResultsFilePath As String

		ResultsFilePath = "C:\Test\TestResults\Iteration" & Iteration & ".xlsx"
		My.Computer.FileSystem.CopyFile("C:\Test\Test.xlsx", ResultsFilePath)

		oExcel = New Excel.Application									 'Create a new instance of Excel

		Dim books As Excel.Workbooks = oExcel.Workbooks
		oBook = books.Open(ResultsFilePath)			 'Get correct file
		ReleaseCOM(books)

		Dim sheets As Excel.Sheets = oBook.Worksheets
		oSheet = CType(sheets.Item(1), Excel.Worksheet)	'oExcel.Worksheets(1)
		ReleaseCOM(sheets)

		oExcel.Visible = False											 'Don't show it to the user

		Dim ur As Excel.Range = oSheet.UsedRange
		Dim urRows As Excel.Range = ur.Rows
		ReleaseCOM(ur)
		Dim LastRow As Int32 = urRows.Count
		ReleaseCOM(urRows)

		Dim oRange As Excel.Range
		oRange = oSheet.Range("B" & LastRow + 1)
		oRange.Value = "TEST1"
		ReleaseCOM(oRange)
		oRange = oSheet.Range("B" & LastRow + 2)
		oRange.Value = "TEST2"
		ReleaseCOM(oRange)
		oRange = oSheet.Range("B" & LastRow + 3)
		oRange.Value = "TEST3"
		ReleaseCOM(oRange)

		ReleaseCOM(oSheet)
		oBook.Save()
		oBook.Close()
		ReleaseCOM(oBook)
		oExcel.Quit()
		ReleaseCOM(oExcel, True)

	End Sub

[nobbyv]

TnTinMn: thanks again for the help. I thought that did it, but it hung at 669 iterations. Anything else you can think of? It's strange that it seems to perform "better", as before I'd never go past maybe 225 iterations, but it's still not "fixed".

EDIT: Ran it again, failed after 74.

[TnTinMN]

Yeah, this is frustrating. I have one trick left up my sleeve. Run the CopyDatatoReport on a secondary thread so that its context closes out when the thread terminates.

Modify CopyDatatoReport's like this:

Sub CopyDatatoReport(ByVal state As Object)
	Dim Iteration As Integer = CInt(state)

Then "call" CopyDatatoReport like this:

Dim t As New Threading.Thread(AddressOf CopyDatatoReport)
t.SetApartmentState(Threading.ApartmentState.STA)
Dim Iteration As Integer = 4
t.Start(Iteration)
t.Join()	' wait for the thread to finish

[wes4dbt]

I had a problem that the Excel process was not actually being closed. You could open the Task Manager->Processes and see multiple Excel instances running. I don't know if this is your problem, but here is how I solved the problem.

            xlApp.Quit()
            GC.Collect()
            GC.WaitForPendingFinalizers()

            Marshal.FinalReleaseComObject(xlWorkSheet)
            Marshal.FinalReleaseComObject(xlWorkBook)
            Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing

[FunkyDexter]

Be cautious with WaitForPendingFinalizers. It does exactly what it says, it waits. And if something is failing to finalize then, far from curing your hang, it will cause it.

When I've been faced with excel ghosts being left behind in the task manager I've found the most important thing is to make sure all the elements in the spread sheet are properly released and they can be really hard to find because excel has an annoying tendency to create them implicitly - particularly ranges. I didn't look too closely at your code but it looks like your releasing the stuff you create explicitly but you may well be missing some implicit stuff.

Personally I avoid explicitly kicking the GC if I can. The fact that you feel the need to kick it means you're probably kicking a problem down the road. My advice would be to try removing all the GC code and watch what happens. Chances are you'll see a bunch of excel processes build up in task manager and then they'll quietly disappear about 10 seconds later.

[TnTinMN]

I should have asked the OP for clarification on the usage of "Hang". Based on the description, I'm 99% certain that it is the classical meaning in that the Worokbooks.Open statement never returns and raises no error. But it would be good to get that resolved.

Also if it is a True hang, perhaps it is because Excel is waiting for response to some dialog. You would see this if you made it visible. One other thing to try is to set the Application.DisplayAlerts property to False. This should suppress most dialogs and return an error.

[nobbyv]

All,
Thanks for the help on this. TnTinMn: BTW, you were correct, "hang" in this case meant the WorkBooks.Open never returns, and there's no error.

In the end, I decided simply to work around the issue by using an array to do temp storage, which I then write off to a file AFTER I've processed it. I will revisit this later, but will use this workaround for now.