Excel process doesn't end in VBA

[irish634]

I have a database where I am importing an excel file.
If I open the database, import the data, it works fine.
However, if I have to import a second file the process fails on this line:

oWKS.Sort.SortFields. _
        Add2 Key:=Range("M2:M" & oWKS.UsedRange.Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

If I close the databse after the initial import, I can again import the data.
I believe the issue I have is my instance of Excel is remaining open as a background process after the code runs.
For the life of me I cannot seem to figure out where I am not killing the instance of excel.



Can anyone see where I am leaving it open?

Private Sub cmd_ImportNCM_Click()
'On Error GoTo Error_Handler

    DoCmd.Hourglass True            'Change the cursor

    '------------------------------------
    'SELECT THE EXCEL FILE TO IMPORT
    '------------------------------------
    
    'Opens the File Dialog
    Set FD = Application.FileDialog(msoFileDialogFilePicker)                    'Set the FD object as a file picker dialog
        With FD
            .AllowMultiSelect = False                                           'Allow the user to select only one file
            .Title = "Please select the Excel file to import..."                'Set the file dialog box title
            .InitialFileName = Environ("USERPROFILE") & "\"                     'Set the dialog starting location
            .Filters.Clear                                                      'Clear the filter list
            .Filters.Add "Excel 2003", "*.xls"          'to add filters, the contents of the function need to be in the procedure
            '.Filters.Add "Excel 2007", "*.xlsx"        'Not needed now, file output is .xls
            '.Filters.Add "All Files", "*.*"            'We want to limit data selection
        End With
     
    If FD.Show <> 0 Then                        'Show the file dialog box
        sFile = FD.SelectedItems(1)             'Selected item
    Else
        sFile = ""                              'If nothing selected, empty string
        sPrompt = "No file was selected; data import canceled."         'Construct the message box PROMPT
        sTitle = "Data Import Canceled..."                              'Construct the message box TITLE
        Beep                                                            'Sound an audible beep
        MsgBox sPrompt, vbExclamation, sTitle                           'Display the message box
        DoCmd.Hourglass False                                           'Change the cursor
        Set FD = Nothing                                                'Code Cleanup
        Exit Sub                                                        'Exit the routine
    End If
           
    
 
    
    

    '---------------------------------------------------------
    'OPEN THE EXCEL FILE AND MAKE SURE IT IS THE CORRECT FILE
    '---------------------------------------------------------
    Dim oXL As Object                       'Excel Application Object
    Dim oWBK As Object                        'Excel Workbook Object
    Dim oWKS As Object                         'Excel Worksheet Object
    
        Set oWKS = Nothing          'Code Cleanup
    Set oWBK = Nothing          'Code Cleanup
    Set oXL = Nothing           'Code Cleanup
    
    Set oXL = New Excel.Application                     'Set the Excel object
    Set oWBK = oXL.Workbooks.Open(sFile)                'Open the Excel File
    oXL.Visible = False                                 'Hides the Excel window
    'oWBK.Activate                                      'Activate the open workbook
    Set oWKS = oWBK.Sheets(1)                       'Set the worksheet
    'oWKS.Activate                                       'Activate
    
    If oWKS.Name <> "qryNonconforming_Ticket_Report_" Then      'If the workbook does not have the right sheet
        sPrompt = "The Excel file selected is not the correct file." & vbCrLf           'Construct the message box PROMPT
        sPrompt = sPrompt & "Please select the try again and select the correct file."  'Construct the message box PROMPT
        sTitle = "Incorrect File Selected..."       'Construct the message box TITLE
        Beep                                        'Sound an audible beep
        MsgBox sPrompt, vbExclamation, sTitle       'Display the message box
        oWBK.Close                                  'Close the workbook
        oXL.Quit
        Set oWKS = Nothing                          'Code Cleanup
        Set oWBK = Nothing                          'Code Cleanup
        Set oXL = Nothing                           'Code Cleanup
        DoCmd.Hourglass False                       'Change the cursor
        Exit Sub                                    'Exit the routine
    End If
   
    '-------------------------------------------------------
    'IF THE CORRECT FILE, THEN CONTINUE FORMATTING DATA
    '-------------------------------------------------------
    oWKS.Range("A:A").EntireColumn.Delete xlShiftToLeft         'Delete the first column
    oWKS.Range("A:A").EntireColumn.Delete xlShiftToLeft         'Delete the first column
    oWKS.Range("A:A").EntireColumn.Delete xlShiftToLeft         'Delete the first column
    
    'Rename the colum headers for importing
    oWKS.Range("A1") = "txt_NCM_NoticeNo"
    oWKS.Range("B1") = "txt_NCM_PartNo"
    oWKS.Range("C1") = "txt_NCM_WorkOrderNo"
    oWKS.Range("D1") = "txt_NCM_LastOperation"
    oWKS.Range("E1") = "txt_NCM_Department_FK"
    oWKS.Range("F1") = "lng_NCM_Quantity"
    oWKS.Range("G1") = "txt_NCM_Defect"
    oWKS.Range("H1") = "txt_NCM_Disposition"
    oWKS.Range("I1") = "dbl_NCM_UnitCost"
    oWKS.Range("J1") = "txt_NCM_DepartmentNo_FK"
    oWKS.Range("K1") = "dtm_NCM_Date"
    oWKS.Range("L1") = "dtm_NCM_EntryDate"
    oWKS.Range("M1") = "dtm_NCM_DispositionDate"
    
    'Sort the data by disposition date
    oWKS.Cells.Select
    oWKS.Sort.SortFields.Clear
    oWKS.Sort.SortFields. _
        Add2 Key:=Range("M2:M" & oWKS.UsedRange.Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    oWKS.Sort.SetRange Range("A1:M" & oWKS.UsedRange.Rows.Count)
    oWKS.Sort.Header = xlYes
    oWKS.Sort.MatchCase = False
    oWKS.Sort.Orientation = xlTopToBottom
    oWKS.Sort.SortMethod = xlPinYin
    oWKS.Sort.Apply

    
    oWBK.Save                   'Save the changes
    oWBK.Close                  'Close the workbook
    oXL.Quit                    'Quit the excel application
    
    Set oWKS = Nothing          'Code Cleanup
    Set oWBK = Nothing          'Code Cleanup
    Set oXL = Nothing           'Code Cleanup
    
    
    '--------------------------------------------------------
    'IMPORT THE NCM DATA
    '--------------------------------------------------------
    Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel8, "BE_tbl_nonconformances", sFile, True)    'Import the spreadsheet
    
    '-------------------------------------------------------
    'CHECK FOR DUPLICATES AND DELETE
    '-------------------------------------------------------
    DoCmd.SetWarnings False                                 'Turn off warnings
    sSQL = "DELETE * FROM FE_tbl_TEMP_RecID;"               'Deletes the records in the temp table
    DoCmd.RunSql sSQL
    DoCmd.OpenQuery "qry_APPEND_NCMDuplicates"
    DoCmd.OpenQuery "qry_DELETE_NCMDuplicates"
    DoCmd.SetWarnings True
    
        
    '-------------------------------------------------------
    'DISPLAY SUCCESS MESSAGE
    '-------------------------------------------------------
    sPrompt = "Data has been successfully imported"     'Construct message box PROMPT
    sTitle = "Success..."                               'Construct message box TITLE
    Beep                                                'Sound an audible beep
    MsgBox sPrompt, vbInformation, sTitle               'Display the message box
    
Error_Handler_Exit:
    DoCmd.Hourglass False                           'Change the cursor
    Set oWKS = Nothing                              'Code Cleanup
    Set oWBK = Nothing                              'Code Cleanup
    Set oXL = Nothing                               'Code Cleanup
    Exit Sub                                        'Exit the routine

Error_Handler:
    Call Error_Log(Err.Number, Err.Description, Me.Name, Me.ActiveControl.Name, True)    'Calls the Error Log function to record an error.
    Err.Clear                                       'Clears the error
    Resume Error_Handler_Exit                       'Exits the routine
    
End Sub

[June7]

Create another procedure that builds code a stage at a time. Start with code that just opens and closes Excel and workbook. When that works, then add lines that manipulate worksheet but just a few at a time.

Instead of

Set oXL = New Excel.Application

try

Set oXL = CreateObject("Excel.Application")

or maybe

' Establish an EXCEL application object
 On Error Resume Next
 Set oXL = GetObject(, "Excel.Application")
 If Err.Number <> 0 Then
       Set oXL = CreateObject("Excel.Application")
       blnEXCEL = True
 End If
 Err.Clear
 On Error GoTo 0

[irish634]

Create another procedure that builds code a stage at a time. Start with code that just opens and closes Excel and workbook. When that works, then add lines that manipulate worksheet but just a few at a time.

Thanks, I did this.
The application remained open on this line:

oWKS.Sort.SortFields. _        
Add2 Key:=Range("M2:M" & oWKS.UsedRange.Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal

So... What I discovered.... Recording a macro in Excel then pasting in Access caused the problem.
I changed this:

 oWKS.Sort.SortFields. _         Add2 Key:=Range("M2:M" & oWKS.UsedRange.Rows.Count), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
 oWKS.Sort.SetRange Range("A1:M" & oWKS.UsedRange.Rows.Count)
 oWKS.Sort.Header = xlYes
 oWKS.Sort.MatchCase = False
 oWKS.Sort.Orientation = xlTopToBottom
 oWKS.Sort.SortMethod = xlPinYin
 oWKS.Sort.Apply

to this:

    oWKS.Columns("A:M").Sort Key1:=oWKS.Columns("M"), Order1:=xlAscending, Header:=xlYes
    oWKS.Range("A1").Select

This took care of the issue. The application quits as it should now.
Thanks for pointing me.

[June7]

I am getting "Object does not support that method" error on that line.

Why are you using Add2 instead of Add? The latter does not get that error for me.

However, I am getting the residual process issue.

Research indicates this is usually caused by an unqualified reference, which I do not see in your code. So the only other recourse I can see is Kill. https://www.devhut.net/2015/05/28/ms...ill-a-process/

Now I see you found solution.

[irish634]

I am getting "Object does not support that method" error on that line.

Why are you using Add2 instead of Add? The latter does not get that error for me.

However, I am getting the residual process issue.

Research indicates this is usually caused by an unqualified reference, which I do not see in your code. So the only other recourse I can see is Kill. https://www.devhut.net/2015/05/28/ms...ill-a-process/

Now I see you found solution.

Using Add2 vs Add was how the Excel macro recorded it. I just copied and pasted, then amended the code to reflect my oWKS object.

I also saw the multitude of posts about an unqualified reference, but I could not see where I had one.

I didn't necessarily want to use "Kill" because if I or another user has a different workbook open (other than what is being imported), I didn't want to mess with that.

I stumbled on a post somewhere that showed a different way to sort the data; I tried it and it solved all the issues. So try my solution and see what you come up with. I have been testing it for the last hour and it works every time.

I should have known to try one line at a time, but I had been programming most of the day, so I was kind of fried. LOL.
Thanks again.

[June7]

Interesting, my macro recorder generated just Add, not Add2. Macro recorder is often how I build new code for Excel automation and, as you discovered, isn't always perfect. Done my share of 'stumbling'


I did try your solution (and saved it in my testing db).