Solved Timing or other issue when closing Excel from Access

[sxschech]

I thought this code was working properly, however, yesterday noticed that the excel file was showing as locked and excel wasn't open (from the perspective of the taskbar not having an underline below the icon). When I went into Task Manager, Excel was there under Background Processes. I think the reason I hadn't encountered previously was that I usually had another excel file open and the code is instructed not to close excel if other files are open. I found when I stepped through the code, Excel closed properly. Leading me to think it was a timing issue. I tried do events and made a visit to Mr. GPT and we had a dialogue with suggestions to use timer and loop which were unsuccessful. The only method I got to work other than stepping through the code was via MsgBox. Once the message box became visible, I could see the lock file disappear as well as Excel no longer showing up in the Background Process.

Are there other suggestions to sort this without the need to click a message box or other form?

Function ExportPDF(xlFileName As String, SavePDF As String, Optional DisplayPDF As Boolean)
'Code to save xlsx as pdf
'Note: If file exists, it is automatically replaced
'https://access-programmers.co.uk/forums/showthread.php?t=228641
'2018114
    Dim objXl As Object
    
    Const xlTypePDF = 0
    Const xlQualityStandard = 0
    
    ' Establish an EXCEL application object
    On Error Resume Next
    Set objXl = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set objXl = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    With objXl
        .Workbooks.Open (xlFileName)
          
        .ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "" & SavePDF & "", quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
            DisplayPDF
        '.Quit
    End With
    'https://www.mrexcel.com/board/threads/quit-current-workbook-but-not-other-active-workbooks.1088914/
    '20240531
    DoEvents
    
    If objXl.Workbooks.Count = 1 Then
        objXl.Quit
        Set objXl = Nothing
        ' Give Excel more time to fully release the resources
        ' Couldn't figure out how to fully release excel using
        ' doevents or loop so ended up with a msgbox
        ' 20250116
        MsgBox "Since there are no other workbooks open, Closing Excel", vbInformation + vbOKOnly, "Timing Issue on Close"
        
    Else
        objXl.ActiveWorkbook.Close
        DoEvents
    End If
End Function

 

[Gasman]

I would just open another Excel instance and then close that and set to nothing.

 

[The_Doc_Man]

I noted that you open the workbooks but don't close them before you attempt the .Quit, and yet you KNOW that the workbook IS open here...

    If objXl.Workbooks.Count = 1 Then
        objXl.Quit
        Set objXl = Nothing<br>

Just for snorts and giggles I would try to close the workbook before you quit the app. Whether you attempt to save or abandon any changes is up to you. This is a guess, but "dangling" Excel instances is not an unknown problem.

 

[sxschech]

Made some changes and added the Close statement. The timing issue seems to have moved up the chain...
...was able to get rid of the message box, however had to repeat the Set Statement to avoid an error on wb.Activate, probably also relating to the timing since the second one doesn't generate an error and the rest of the code completes.

Run-time error '-2147417848 (80010108)'
Automation error
The object invoked has disconnected from its clients

Function ExportPDF(xlFileName As String, SavePDF As String, Optional DisplayPDF As Boolean)
'Code to save xlsx as pdf
'Note: If file exists, it is automatically replaced
'https://access-programmers.co.uk/forums/showthread.php?t=228641
'2018114
'Modified for closing file and releasing lock when
'only one file open
'20250117
    Dim objXl As Object
    Dim wb As Object
    Const xlTypePDF = 0
    Const xlQualityStandard = 0
    
    ' Establish an EXCEL application object
    On Error Resume Next
    Set objXl = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set objXl = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    objXl.Visible = True
    Set wb = objXl.Workbooks.Open(xlFileName, True, False)
    DoEvents
    'Second set statement to overcome automation error (timing issue)
    Set wb = objXl.Workbooks.Open(xlFileName, True, False)
    wb.Activate
    wb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "" & SavePDF & "", quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
            DisplayPDF
    'https://www.mrexcel.com/board/threads/quit-current-workbook-but-not-other-active-workbooks.1088914/
    '20240531
    DoEvents
    
    If objXl.Workbooks.Count = 1 Then
        wb.Close
        objXl.Quit
        Set wb = Nothing
        Set objXl = Nothing
    Else
        wb.Close
        Set wb = Nothing
    End If
End Function

 

[The_Doc_Man]

The error in question says that the object you tried to use via Component Object Model techniques is now a de-referenced object i.e. if you examined it with the debugger, it would say "Nothing" (which is the object equivalent of Null).

 

[sxschech]

So is okay the way the code is then?

 

[cheekybuddha]

' ...
    wb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "" & SavePDF & "", quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
            DisplayPDF
' ...

If DisplayPDF is True, then it is likely Excel will remain as a running task until the PDF is closed (since it is opened by the Excel process). Closing the PDF file *should* allow the Excel process to terminate. (I haven't tested this!)

You could alternatively, always pass False to the DisplayPDF parameter, and open the PDF from Access in the next line of code. Then, when you Quit objXL, it should do so cleanly.

 

[The_Doc_Man]

I wonder about this:

    If objXl.Workbooks.Count = 1 Then
        wb.Close
        objXl.Quit
        Set wb = Nothing
        Set objXl = Nothing
   Else
        wb.Close
        Set wb = Nothing
    End If

Can objXL.Workbooks.Count ever be > 1? Object wb will always be either defined and pointing to an open workbook, or not defined and pointing to Nothing. It is a single object and thus is ALWAYS singular. But objXL.Workbooks can legally be plural.

 

[cheekybuddha]

Can objXL.Workbooks.Count ever be > 1?

If Excel was already open and objXL is set by the .GetObject, then yes (if there is already a workbook open in Excel)

 

[arnelgp]

you can also create an instance of excel and leave it open while your db is open.
it will get closed when you close your database.

Function ExportPDF(xlFileName As String, SavePDF As String, Optional DisplayPDF As Boolean)
'Code to save xlsx as pdf
'Note: If file exists, it is automatically replaced
'https://access-programmers.co.uk/forums/showthread.php?t=228641
'2018114
'Modified for closing file and releasing lock when
'only one file open
'20250117
    Static objXl As Object
    Dim wb As Object
    Const xlTypePDF = 0
    Const xlQualityStandard = 0
    
    ' Establish an EXCEL application object
    On Error Resume Next
    ' arnelgp
    ' 01/18/2025
    ' just create an instance of excel
    ' and leave it open
    ' don't worry, when you close the
    ' db it will also get closed
    If objXl Is Nothing Then
        Set objXl = CreateObject("Excel.Application")
    End If
    
    ' why do you need to make it visible?
    objXl.Visible = True
    Set wb = objXl.Workbooks.Open(xlFileName, True, False)
    DoEvents
    'Second set statement to overcome automation error (timing issue)
    Set wb = objXl.Workbooks.Open(xlFileName, True, False)
    wb.Activate
    wb.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "" & SavePDF & "", quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
            DisplayPDF
    'https://www.mrexcel.com/board/threads/quit-current-workbook-but-not-other-active-workbooks.1088914/
    '20240531
    DoEvents
    wb.Close
    Set wb = Nothing
    
    'arnelgp
    'i hid it again here
    objXl.Visible = False
End Function

 

[The_Doc_Man]

If Excel was already open and objXL is set by the .GetObject, then yes (if there is already a workbook open in Excel)

Having an unsaved object still open can prevent Excel from quitting when you tell it to do so. You can do something similar to

DIM WB As Excel.WorkBook
...

For Each WB in objXL.Workbooks
    WB.Close SaveChanges := False
Next WB
objXl.Quit

This closes every workbook and Excel itself (in that order). If there is the odd chance that you will have some workbook open from another app and cannot shut that down from this instance of Access, then you have to have a way to know that the last Excel-using App has a clear field to close everything and you can't allow yourself to be bothered by having Excel still open.

 

[sxschech]

Thanks for the various suggestions. I thought things were working when I left on Friday, however today, any variation of the code still presented issues. After a bit more searching, today, I think I discovered why the original code had worked fine until last week and seemed to work on Friday after using The Doc Man and ArnelGP suggestion/code once again did not work today. The sheet I had open when things stopped working turned out to be in Protected View. Turns out that a sheet opened in Protected View is not included in Workbooks.Count. Found out about and added the ProtectedViewWindows.Count to get the total number of open excel files.

Once I did that, decided to go back to my original code and remove all the doevents and message boxes and so far, has been error free. Tested with no files open, one file normal, one protected file and a combination of protected and unprotected.

Changed this:
If objXl.Workbooks.Count = 1 Then

To this:
If objXl.Workbooks.Count + objXl.ProtectedViewWindows.Count = 1 Then


Regarding ArnelGP's comment about why visible=true, original code didn't have it, I tried putting it in to see if it made a difference to see what was going on.

Regarding Cheekybuddha's comment about

If DisplayPDF is True, then it is likely Excel will remain as a running task until the PDF is closed (since it is opened by the Excel process). Closing the PDF file *should* allow the Excel process to terminate. (I haven't tested this!)

You could alternatively, always pass False to the DisplayPDF parameter, and open the PDF from Access in the next line of code. Then, when you Quit objXL, it should do so cleanly.

Indeed, this is set to False when the function is called.

Call ExportPDF(stCoverSheet, Replace(stCoverSheet, ".xlsx", ".pdf"), False)

Here is the original code that now seems to work after adding the ProtectedView count and the Close per The Doc Man's post #3.

Function ExportPDF(xlFileName As String, SavePDF As String, Optional DisplayPDF As Boolean)
'Code to save xlsx as pdf
'Note: If file exists, it is automatically replaced
'https://access-programmers.co.uk/forums/showthread.php?t=228641
'2018114
    Dim objXl As Object
    
    Const xlTypePDF = 0
    Const xlQualityStandard = 0
    
    Set objXl = CreateObject("Excel.Application")
    
    With objXl
        .Workbooks.Open (xlFileName)
          
        .ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "" & SavePDF & "", quality:=xlQualityStandard, _
            includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
            DisplayPDF
        '.Quit
    End With
    'https://www.mrexcel.com/board/threads/quit-current-workbook-but-not-other-active-workbooks.1088914/
    '20240531
    'Protected view is not included in Workbooks.Count so added the ProtectedViewWindows.Count
    'to get the total number of open excel files.  Because Protected view files weren't counted
    'the code was closing files and sometimes generated other errors
    '20240121
    If objXl.Workbooks.Count + objXl.ProtectedViewWindows.Count = 1 Then
        objXl.ActiveWorkbook.Close
        objXl.Quit
    Else
        objXl.ActiveWorkbook.Close
    End If
    
    Set objXl = Nothing
End Function

 

[The_Doc_Man]

Correcting the count and implementing a close probably had or at least contributed to the desired result. While there is some controversy on just how nit-picky you should be, there is this old book I like to reference sometimes: Everything I Needed To Know, I Learned In Kindergarten. In it, there is the sage advice: If you open it, close it.

 

[sxschech]

It was a careless oversight on my part when I added the If statement logic to test for whether to keep excel open. Probably did ctrl+x rather than ctrl+c when pasting into the Else.