Opening Excel Workbook Causes Sporadic Hang

nobbyv

New member
Joined
Jan 29, 2016
Messages
1
Programming Experience
10+
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 Excelprocesses 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.
VB.NET:
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.Application
    Dim oSheet As Excel.Application

    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 fromExcel and end processes
    oExcel.Quit()
    oBook = Nothing
    oExcel = Nothing
    GC.Collect()
      GC.WaitForPendingFinalizers()
 
Top Bottom