how to check if excel file was closed
https://learn.microsoft.com/en-us/office/troubleshoot/office-suite-issues/getobject-createobject-behaviorOption Explicit
Function isXlOpen(fullFileName) As Boolean
Dim xlApp As Object
Dim errNo As Long
On Error Resume Next
Set xlApp = GetObject(fullFileName).Application
errNo = Err
On Error GoTo 0
If errNo = 432 Then
isXlOpen = False
Else
isXlOpen = True
End If
Set xlApp = Nothing
End Function
Sub test()
Debug.Print isXlOpen("C:Temp\Sample.xlsx")
End Sub
I don't see how I could get this information without intercepting events (such as Workbook.BeforeClose event). Am I missing something?
That second part has nothing to with previous one.
The first (recommended) checks independently if the file is open.
The second method requires a control over the life cycle of the excel object.
You would create the object, attach an eventlistener, and handle the event beforeclosed accordingly.
More complicated and prone to unforeseen exceptions.
To make it short.
In your UI (for example a form), you would display
- a hint like "Press button OPEN to open the excel file. Do so and so, then save and close the file".
- the according button with the code to open the excel file.
- a second hint "When editing finished, press the button ''Post Processing'' ".
- and the button "Post Processing". Here you'll check if the file is closed (cf code above), then open and read it for your actual post processing. When finished reading close the file and kill the excel app.
