CODEWITHSUNDEEP
excelvbams-word
cohara
cohara

Reputation: 111

Close Excel Application if Word crashes while reading from Excel file

I am trying to ensure that an Excel file is not left open if a Word application that is gathering data from it via a macro suddenly crashes.

The macro handles most errors using the code below, but I want to ensure that the Excel file isn't left hanging open in the face of an unhandled event. Such an event might be the user closing the word document from the task manager while the macro is running.

Public Function GatherDataFromExcel(x) As Collection

    On Error GoTo CloseExcel

    Dim path_file_excel As String
    path_file_excel = "\path to file\file.xlsx"

    Dim objXL As Object ' Excel.Application
    Dim objBook As Object ' Excel.Workbook

    Set objXL = CreateObject("Excel.Application")
    On Error Resume Next

    Set objBook = objXL.Workbooks.Open(path_file_excel)
    On Error Resume Next

    'Code that gathers data and returns that data from the function

CloseExcel:

    If Not objBook Is Nothing Then
        objBook.ActiveWorkbook.Close
        objBook.Close
        Set objBook = Nothing
        If Not objXL Is Nothing Then
            Set objXL = Nothing
        End If
    End If

End Function

I have closed the word document mid execution to check, and indeed the excel file is left hanging open. Is there any way to prevent this from happening?

Upvotes: 0

Views: 111

Answers (1)

Digiwise
Digiwise

Reputation: 121

If Word crashes you are going to need a different process to close Excel.

A few options:

  1. Show Excel to the user so that the user can close Excel objXL.visible = true

  2. If your VBA error handler does run you probably want to change:

    objBook.ActiveWorkbook.Close
objBook.Close

    To

    objBook.Close False ' Close workbook without saving changes
objXL.Quit ' Exit Excel
  3. As Freeflow suggested, have another process (e.g. timer event in Excel or a separate VBScript process) that closes Excel if it can't find the Word document

Upvotes: 1

Related Questions