Excel application not closing from Outlook VBA function

Question

I'm writing a sort of homegrown ticketing system for myself in Outlook VBA, and I'm using Excel to store all the persistant data. I have a function written in Outlook to get some data from the .csv and return it. This is all working fine, but after I close the workbook, quit the application, and set the app to nothing I still have an Excel process running! Here is my code:

Private Function GetNewTicketNumber() As Integer
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        .EnableEvents = False
        .DisplayAlerts = False
    End With
    Dim FileStr As String
    Dim NumberBook As Workbook
    Dim TheRange As Range
    FileStr = "C:\OMGITSAPATH.csv"
    Set NumberBook = Workbooks.Open(FileStr)
    Set TheRange = NumberBook.Worksheets(1).Range("A1")
    GetNewTicketNumber = TheRange.Value
    TheRange.Value = TheRange.Value + 1
    NumberBook.Save
    NumberBook.Close
    xlApp.Quit
    With xlApp
        .Visible = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set xlApp = Nothing
End Function

Is there something that I'm doing wrong here? My problem is similar to the one here, but I have disabled DisplayAlerts... What can I do to fix this problem?

There will be an Excel icon on my taskbar that will not open. Even if I minimize all my other windows nothing comes up. — Telestia, Jun 23 '14 at 20:38
Another thing to test, comment out your last `With xlApp` block. If it doesn't help the issue, it may be unneeded as those settings will reset to defaults when you run the app again. — Jimmy Smith, Jun 23 '14 at 20:43
Well I set .Visible to True and now the icon isn't showing up anymore. @Jimmy Smith I did that and nothing (that I could see) changed. — Telestia, Jun 23 '14 at 20:46
I have seen cases where that .Quit runs asynchronously with the next lines of code. My theory was that it could potentially throwing an alert as it's getting set true before closing. In any case, that block shouldn't be necessary unless you're reopening the xlApp to do more. — Jimmy Smith, Jun 23 '14 at 20:49
Update: When I opened an unrelated Excel doc this morning I got the document recovery box with 6 instances of the file from above. I don't know if this makes a difference or not though. — Telestia, Jun 24 '14 at 13:45
@Telestia No. This would have been Excel giving you the option of recovering all workbooks from the Excel processes from your code attempts. — brettdj, Jul 06 '14 at 01:37

score 3 · Accepted Answer · answered Jul 06 '14 at 01:34

Try fully qualifying your references to Excel, from xl_doesnt_quit

The problem presented here is exactly what you have. This line
Range("a1").Value = Range("a1").Value + 1
leave the xl instance open

The most common cause of the problem is a 'global' reference to the automated application. Unfortunately, under some circumstances it is possible to directly refer to an entity (property/method/object) of the automated object. This reference effectively is global to the calling application. Hence, the reference remains in place as long as the calling program is active. Consequently, the operating system will not end the automated application while the caller is active.

Re-cut code below (which also uses late binding - which rules out the unqualified possibility).

Pls change you path to suit.

code

  Private Function GetNewTicketNumber() As Long
    Dim xlApp As Object
    Dim objWB As Object
    Dim objWs As Object
    Dim FileStr As String

    FileStr = "C:\temp\test.xlsx"

    Set xlApp = CreateObject("excel.application")

    With xlApp
        .EnableEvents = False
        .DisplayAlerts = False
    End With

    Set objWB = xlApp.Workbooks.Open(FileStr)
    Set objWs = objWB.Sheets(1)
    GetNewTicketNumber = objWs.Range("A1")
    objWs.Range("A1") = objWs.Range("A1") + 1

    objWB.Save
    objWB.Close

    Set objWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
End Function

Excel application not closing from Outlook VBA function

1 Answers1

Linked