Excel isn't closing

Question

I am running the following working Macro on word visual basic. Each time I run it, the macro successfully generates the report how I want it to; but then I look in the task manager and I see that an instance of excel is still running. I run the debugger over the code, the debugger goes through the final line:

oExcel.quit 

and yet it still doesn't terminate the application!

Sub WriteExtension()
'
' WriteExtension Macro
'
'
        copyFile

        Dim nWord As New Document
        word.Application.ScreenUpdating = False
        Set nWord = Documents.Open("c:\output\report\here\report", Visible:=False)

        'initialize excel variables
        Dim oExcel As Excel.Application
        Dim oWorkbook As workbook
        Dim oWorksheet As worksheet

        'initialize excel object
        Set oExcel = New Excel.Application
        oExcel.ScreenUpdating = False
        Set oWorkbook = oExcel.Workbooks.Open("c:\spreadsheet\here\spreadsheet.xlsx")
        Set oWorksheet = oWorkbook.Worksheets(Sheets("Extensions").Index)
        'setup loop variables

        Dim tempString As String
        Dim delim As String

        Dim i As Long
        Dim bkMark As Bookmark
        Dim questions(13) As String

        questions(0) = 13
        questions(1) = 15
        questions(2) = 17
        questions(3) = 19
        questions(4) = 29
        questions(5) = 31
        questions(6) = 33
        questions(7) = 36
        questions(8) = 38
        questions(9) = 40
        questions(10) = 42
        questions(11) = 46
        questions(12) = 48

        delim = "#"

        tempString = delim & Join(questions, delim)

        Dim bmrange As Range

        For i = 1 To 78

            If (InStr(1, tempString, delim & i & delim, vbTextCompare)) Then
                Set bmrange = nWord.Bookmarks("BM" & (i)).Range
                If (Cells(4, i + 6) = 1) Then
                    nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = True
                Else
                    nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = False
                End If

            ElseIf (InStr(1, tempString, delim & (i - 1) & delim, vbTextCompare)) Then

                Set bmrange = nWord.Bookmarks("BM" & (i)).Range
                If (Cells(4, i + 6) = 1) Then
                    nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = True
                Else
                    nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = False
                End If
            Else
                nWord.Bookmarks.Item("BM" & i).Range.InsertAfter (Cells(4, i + 6))

            End If

        Next i

        Dim filePath As String
        Dim fileName As String
        Dim newName As String

     '   save the file as a PDF and close the PDF
        filePath = "c:\output\report\here\report"
        fileName = Cells(4, 13) & Cells(4, 12) & Cells(4, 79) & ".pdf"
        newName = filePath & fileName
        nWord.SaveAs2 fileName:=newName, FileFormat:=wdFormatPDF

    '   Close things
        nWord.Close False
        oWorkbook.Close False
        oExcel.Quit

End Sub

Answer 1

I suspect that your issue is related to your unqualified Sheets and Cells references.

Set oWorksheet = oWorkbook.Worksheets(Sheets("Extensions").Index) should probably just be Set oWorksheet = oWorkbook.Worksheets("Extensions") (no need to get the index of a sheet by using its name just to get a reference to the sheet, when you can just index it by its name) and Cells(4, i + 6) should probably be oWorksheet.Cells(4, i + 6).

I could replicate your issue before I made those changes (although sometimes the code would just crash), but once I fixed them Excel correctly closed at the End Sub. (It didn't disappear after the oExcel.Quit because oExcel wasn't Nothing yet.)

Sub WriteExtension()
'
' WriteExtension Macro
'
'
    copyFile

    Dim nWord As New Document
    word.Application.ScreenUpdating = False
    Set nWord = Documents.Open("c:\output\report\here\report", Visible:=False)

    'initialize excel variables
    Dim oExcel As Excel.Application
    Dim oWorkbook As workbook
    Dim oWorksheet As worksheet

    'initialize excel object
    Set oExcel = New Excel.Application
    oExcel.ScreenUpdating = False
    Set oWorkbook = oExcel.Workbooks.Open("c:\spreadsheet\here\spreadsheet.xlsx")
    Set oWorksheet = oWorkbook.Worksheets("Extensions")
    'setup loop variables

    Dim tempString As String
    Dim delim As String

    Dim i As Long
    Dim bkMark As Bookmark
    Dim questions(13) As String

    questions(0) = 13
    questions(1) = 15
    questions(2) = 17
    questions(3) = 19
    questions(4) = 29
    questions(5) = 31
    questions(6) = 33
    questions(7) = 36
    questions(8) = 38
    questions(9) = 40
    questions(10) = 42
    questions(11) = 46
    questions(12) = 48

    delim = "#"

    tempString = delim & Join(questions, delim)

    Dim bmrange As Range

    For i = 1 To 78

        If (InStr(1, tempString, delim & i & delim, vbTextCompare)) Then
            Set bmrange = nWord.Bookmarks("BM" & (i)).Range
            If oWorksheet.Cells(4, i + 6) = 1 Then
                nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = True
            Else
                nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = False
            End If

        ElseIf InStr(1, tempString, delim & (i - 1) & delim, vbTextCompare) Then

            Set bmrange = nWord.Bookmarks("BM" & (i)).Range
            If oWorksheet.Cells(4, i + 6) = 1 Then
                nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = True
            Else
                nWord.ContentControls.Add(wdContentControlCheckBox, bmrange).Checked = False
            End If
        Else
            nWord.Bookmarks.Item("BM" & i).Range.InsertAfter (oWorksheet.Cells(4, i + 6))

        End If

    Next i

    Dim filePath As String
    Dim fileName As String
    Dim newName As String

 '   save the file as a PDF and close the PDF
    filePath = "c:\output\report\here\report"
    fileName = oWorksheet.Cells(4, 13) & oWorksheet.Cells(4, 12) & oWorksheet.Cells(4, 79) & ".pdf"
    newName = filePath & fileName
    nWord.SaveAs2 fileName:=newName, FileFormat:=wdFormatPDF

'   Close things
    nWord.Close False
    oWorkbook.Close False
    oExcel.Quit

    'Optional: Set Excel objects to Nothing so that Excel closes now instead of at End Sub
    Set oWorkbook = Nothing
    Set oExcel = Nothing

End Sub