How to terminate Excel process in Outlook VBA program so Excel spreadsheet is not locked?

Question

I've written a program in Outlook VBA which creates emails dependent upon the contents of an Excel spreadsheet.

When the program terminates I continue to have an "EXCEL.EXE" process running which locks the spreadsheet so no-one else can open it.

Within the code I have three Excel objects:

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet

At the end I close the workbook and set all of the variables to Nothing:

xlwb.Close

Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

This is the bare bones of the code including the new "Quit" line:

Dim xl As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem
Set xl = Excel.Application
Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)

For Each xlsheet In xlwb.Worksheets
    for xlrow = 1 to 5
        If xlsheet.Cells(xlRow, 1).Value = "John" Then
           msg=msg & xlsheet.Cells(xlRow, 2).Value
        end if
    next
next

Set Mail = ol.CreateItem(olMailItem)
   
Mail.To = "A@b.c"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

Give us a bare bones demo to repro. People lets try and solve this without resorting to killing the Excel.exe process — Jeremy Thompson, Nov 18 '15 at 10:32
@JeremyThompson OK, I'll add it to the question, give me a few minutes to strip it down.... — Lefty, Nov 18 '15 at 10:44

score 2 · Answer 1 · answered Nov 18 '15 at 10:24

2

you need to Quit the Application xl.Quit the Set "" = Nothing isn't really necessary

answered Nov 18 '15 at 10:24

Alex Gale

55
1
7

Thanks, see my comment to area9's answer. I tend to do the "Nothing" thing as good practice when I remember and I always add it in desperation when I get this kind of problem. It never seems to help! – Lefty Nov 18 '15 at 10:28
Hey mate, +10 cause you're right, the core object model objects aren't being released. Hence the exe has a root ref *somehwere* and the garbage collector cant pick it up and finalize that last method or two. So the GC completes this resurrection process and once its happy can do the malloc operation. – Jeremy Thompson Nov 18 '15 at 10:58
Glad someone else realises this! `Set xl = Nothing` basically detaches the object reference from the variable - it does nothing to the instance of Excel that's been created. Unless you're working with really old VBA, variables are cleared down once they go out of scope anyway so no need for this. – SierraOscar Nov 18 '15 at 12:33

score 1 · Accepted Answer · edited Nov 18 '15 at 12:29

1

xl.quit

This will close the application (you are only closing the workbook and not the application in your code), so just put this before setting the variable to nothing.

Edit: Please change your sub to the following:

Dim xl As New Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim ol As Outlook.Application
Dim Mail As MailItem

Set ol = Outlook.Application
Set xlwb = xl.Workbooks.Open("C:\sheet.xlsx", ReadOnly)
For Each xlsheet In xlwb.Worksheets

For xlRow = 1 To 5

If xlsheet.Cells(xlRow, 1).Value = "John" Then
    msg = msg & xlsheet.Cells(xlRow, 2).Value

End If

Next

Next

Set Mail = ol.CreateItem(olMailItem)

Mail.To = "A@b.c"
Mail.Subject = "John's email"
Mail.Body = msg
Mail.Send

xlwb.Close

xl.Quit

Set ol = Nothing
Set xlsheet = Nothing
Set xlwb = Nothing
Set xl = Nothing

edited Nov 18 '15 at 12:29

SierraOscar

17,507
6
40
68

answered Nov 18 '15 at 10:16

area9

391
2
12

Thanks for this, I've just tried it and it seems to be releasing the spreadsheet now - but the EXCEL process still remains in my task manager. That's obviously a minor problem but it does make me think I'm doing something else wrong...? – Lefty Nov 18 '15 at 10:24
ok, how about this: Excel.Application.quit instead of xl.quit - I don't see you creating an instance of the application in your code. – area9 Nov 18 '15 at 10:36
1

@Macro Man/@area9 Excellent! That actually did it! I just made it "New" and commented the "Set" and that did the job. In words of one syllable, are you able to explain why this works please? – Lefty Nov 18 '15 at 16:32
@Lefty sure, have a [read of this](http://stackoverflow.com/questions/32498851/reason-for-setting-variables-in-vba/32499195#32499195) – SierraOscar Nov 18 '15 at 16:34
@MacroMan That is an excellent explanation of something that's always troubled me: Dim/Set/New. I understand that somewhat better now but will have to re-read a few times I think. So, (if I understand correctly) in your solution, "New" is the important thing, it forces initialisation of the object. Does that mean that it can successfully be destroyed at the end as in Jeremy Thompson's comment about Garbage Collection? – Lefty Nov 18 '15 at 17:03
Garbage Collection is pretty specific to more complex OOP languages - in VBA, assuming you're not working with a really old version - any variables that go out of scope are automatically destroyed. If you've started a _process_ however (such as an application) then you need to explicitly end that process through it's native method (such as `.Quit`) before it's destroyed – SierraOscar Nov 18 '15 at 17:13
"New" creates an instance of the object. if you reference it to a variable, then you can control it: xl.quit, set xl = nothing etc. Think what your code was doing before was just opening a workbook and, after some code, closing it - you had no reference to the application, only the workbook in the application. By referencing the application, you can close that instance and would therefore not affect other excel instances you may have open at the same time. – area9 Nov 18 '15 at 17:18
@area9 All this seems very logical to me - however, issuing the QUIT to Excel DID NOT actually make Excel quit. What did the job was when I changed the Dim to "New" for the "xl" variable. I'm really having trouble understanding that. – Lefty Nov 21 '15 at 08:18

score 0 · Answer 3 · answered Nov 18 '15 at 16:37

You could try something like this

Option Explicit
Sub Excel()
    '//  Declare variables
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlStarted As Boolean


    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        xlStarted = True
    End If



    ' your code here




    '// Close & SaveChanges
    xlWb.Close SaveChanges:=True
    If xlStarted Then
        xlApp.Quit
    End If

    '// clean up
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlSheet = Nothing
 End Sub

How to terminate Excel process in Outlook VBA program so Excel spreadsheet is not locked?

3 Answers3