Excel not closing

[DreamGenius]

As a result of the issue which prevents updating linked spreadsheets from Access, I've had to completely redesign my import routine a requirement of which is Pass/Fail indicators after import to confirm whether or not a record was correctly imported or not.

The process flow goes something like this:

1. Import linked spreadsheet into temporary table using Append query
2. Import temporary table into database tables using Append query
3. Check that records have imported by comparing key values
4. Update Import flag in temporary table
5. Export temporary table out to original Excel file
6. Delete records from temporary table

The challenge is that Excel isn't closing and eventually, after multilple imports, it starts to fail. Ideally, I'd like to open a separate instance of Excel and cleanly close it after but I'm not sure how.

This is the export code, called from code fired on clicking on a button:

Public Function ExportImport(strExport As String) As String
    On Error GoTo Err_ExportImport
 
'   Excel object variables
    Dim appExport As Excel.Application
    Dim wkbExport As Excel.Workbook
    Dim wksExport As Excel.Worksheet
    Dim strImport As String
    Dim strOutput As String
    Dim strTempFile As String
    Dim strFilePath As String
 
    Dim mdbExport As DAO.Database
    Dim setExport As DAO.Recordset
'   Dim strSQL As String
    Dim lngRecords As Long
    Dim intRow As Integer
    Dim intCol As Integer
    Dim intField As Integer
 
    Const cstExportTab As Byte = 1   '   Same sheet as data came out of
    Const cstStartRow As Byte = 2    '   Row 1 contains Column Headings!
    Const cstStartCol As Byte = 1    '   Col 1 is Col A to replace all
 
    DoCmd.Hourglass True
 
'   Set to break on all errors
    Application.SetOption "Error Trapping", 0
 
    strFilePath = "[URL="file://\\Wwp\data\Internal"]\\Wwp\data\Internal[/URL] HR Data\New Structure\Admin services\Learning & Development\Training Records Database\"
    Select Case strExport
        Case "New Requests"
            strImport = strFilePath & "ImportNewRequests.xls"
        Case "Dates Invited"
            strImport = strFilePath & "ImportDatesInvited.xls"
        Case "Dates Attended"
            strImport = strFilePath & "ImportDatesAttended.xls"
        Case Else
        '   Shouldn't happen
    End Select
'   Create the Excel Applicaiton, Workbook and Worksheet and Database object
 
        Set appExport = Excel.Application
'       appExport.Visible = True
        Set wkbExport = appExport.Workbooks.Open(strImport)
        Set wksExport = appExport.Worksheets(strExport)
        Set mdbExport = CurrentDb
        Set setExport = mdbExport.OpenRecordset(strExport, dbOpenSnapshot)
 
        intCol = cstStartCol
        intRow = cstStartRow
        If Not setExport.BOF Then setExport.MoveFirst
        Do Until setExport.EOF
            intField = 0
            lngRecords = lngRecords + 1
 
            For intCol = cstStartCol To cstStartCol + (setExport.Fields.Count - 1)
                wksExport.Cells(intRow, intCol) = setExport.Fields(intField)
                wksExport.Cells(intRow, intCol).WrapText = False
                Debug.Print setExport.Fields(intField).Name & " : " & _
                    setExport.Fields(intField).Value
                intField = intField + 1
            Next
            wksExport.Rows(intRow).EntireRow.AutoFit
            intRow = intRow + 1
            setExport.MoveNext
        Loop
 
    ExportImport = "Total of " & lngRecords & " rows processed."
 
Exit_ExportImport:
'   Cleanup all objects  (resume next on errors)
    On Error Resume Next
        wkbExport.Save
 
        wkbExport.Close
        setExport.Close
        mdbExport.Close
        appExport.Quit
 
        Set wksExport = Nothing
        Set wkbExport = Nothing
        Set appExport = Nothing
        Set setExport = Nothing
        Set mdbExport = Nothing
 
        DoCmd.Hourglass False
        Exit Function
 
Err_ExportImport:
        ExportImport = Err.Description
        Resume Exit_ExportImport
 
End Function

 

[DreamGenius]

Okay, a little tenacity and a lot of Google appears to have solved the problem. I found this on another forum and changed my code to:

1. Specifically ask them to close Excel
2. Open a new Excel object
3. Run the export
4. Close the Excel object

This appears to have done the trick. I'm not seeing any residual instances of Excel lurking in Task Manager > Processes. The key bit was New Excel.Application