Excel Still Running

[boblarson]

Bob, just changed my code to use ActiveSheet; same result.

In a way, I'm tempted to say, "Screw it, let's just blow off Excel with a call to kill process at end of the processing."

But did you use xl.Activesheet?

Remember the NIKE project? I used the range object with no ill effects of it remaining and it didn't require me to set rng = nothing to do so.

 

[Banana]

This is what I used:

Set rng = xl.ActiveSheet.Range("A1")

Excel instance persisted until I set rng to nothing. :\

 

[boblarson]

I just used this code and it released the Excel.EXE object just fine:

Function ExlTest()
    Dim xlApp As Excel.Application
    Dim xlwb As Excel.Workbook
    Dim xlws As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set xlApp = New Excel.Application
    Set xlwb = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlws = xlApp.Worksheets.Add
    
    Set rng = xlApp.ActiveSheet.Range("A1:B3")
    
    xlwb.Close False
    xlApp.Quit
    
End Function

 

[Banana]

Aha, I think I know what is going on!

When I ran your code, I thought that it wasn't killing the Excel instance. But if I let it run to the "End Function" and go past it, the Excel instance dies.

In my earlier testing, I was expecting the Excel instance to die after the line "Set xl = Nothing", and that may be what WayneRyan was expecting as well. But I went back to my old code, commented out all "Set .. = Nothing", and allowed the code to run to the "End Sub", and it would die only after the End Sub has been passed.

Now, within the procedure, if we add the Set ... = Nothing for all declared objects, it can die before we reach the end of procedure, but it's not required to do so because when the procedure ends, it dies anyway.

Whew, that's a relief. I was having the nightmares of ghost objects...

 

[boblarson]

Forgot to mention that the above test released the Excel Application (no longer in Task Manager) as soon as the End Function was reached, not when the xlApp.Quit was run.


EDIT: I just posted this before I saw Banana's response stating almost the same thing

 

[seestevecode]

I'd be really grateful if someone could take a look at the code here to see if they can spot what is keeping Excel alive.

For now, I am calling a KillExcel function, which is doing the job, but I don't want to do it this way.

Private Sub cmdImportWorkflow_Click()

    On Error GoTo Err_cmdImportWorkflow_Click
    
    Dim xlTemp As Excel.Application, _
        wbWorkflow As Excel.Workbook, wbTemp As Excel.Workbook, _
        strTempFilename As String, strRangeForAccess As String, _
        shTmpStaff As Excel.Worksheet, shTmpData As Excel.Worksheet, sh As Excel.Worksheet, _
        rngRecordSet As Excel.Range, _
        db As DAO.Database, qry As DAO.QueryDef, rs As DAO.Recordset, fld As DAO.Field, strSQL As String, _
        dlgChooseWorkflow As FileDialog, varSelectedItem As Variant, _
        a As Integer, i As Integer, iLoop As Integer, _
        intNumberOfActivities As Integer, intNumberOfDataRows As Integer, _
            intDateRow As Integer, intDateColumn As Integer, intFirstDataRow As Integer, _
            intNameColumn As Integer, intFirstCalcDataColumn As Integer, _
        lngNextRow As Long

    'Open dialog to choose Workflow
    Set dlgChooseWorkflow = Application.FileDialog(msoFileDialogFilePicker)
    With dlgChooseWorkflow
    
        .InitialView = msoFileDialogViewLargeIcons
        .InitialFileName = WORKFLOW_LOCATION    'Location set in Globals module
        .AllowMultiSelect = False
        .Title = "Choose a Workflow to import"
        .Filters.Clear
        .Filters.Add "Excel Workbooks", "*.xls"
        If .Show = -1 Then varSelectedItem = .SelectedItems.Item(1): GoTo WorkflowSelected
        
    End With
    
WorkflowSelected:
    'Create Excel instance
    Set xlTemp = New Excel.Application
    With xlTemp
        .Visible = True
        .ScreenUpdating = False
        .DisplayAlerts = False
        
        'Open selected Workflow and assign values
        Set wbWorkflow = .Workbooks.Open(varSelectedItem, , True)
        On Error Resume Next
        With wbWorkflow
            intNumberOfActivities = .Names("NumberOfActivities").RefersToRange.Value
            intNumberOfDataRows = .Names("NumberOfDataRows").RefersToRange.Value
            intDateRow = .Names("DateRow").RefersToRange.Value
            intDateColumn = .Names("DateColumn").RefersToRange.Value
            intFirstDataRow = .Names("FirstDataRow").RefersToRange.Value
            intNameColumn = .Names("NameColumn").RefersToRange.Value
            intFirstCalcDataColumn = .Names("FirstCalcDataColumn").RefersToRange.Value
        End With 'wbWorkflow
        On Error GoTo Err_cmdImportWorkflow_Click
        
        'Create blank workbook
        Set wbTemp = .Workbooks.Add
        
    End With 'xlTemp
    
    On Error Resume Next
    strTempFilename = WORKFLOW_LOCATION & "TempWorkflow.xls"
    With wbTemp
        xlTemp.DisplayAlerts = False
        .SaveAs strTempFilename
        xlTemp.DisplayAlerts = True
        
        Set shTmpStaff = .Sheets(1)
        shTmpStaff.Name = "Staff"
        Set shTmpData = .Sheets(2)
        shTmpData.Name = "Data"
    End With
    On Error GoTo Err_cmdImportWorkflow_Click
     
    'Create database object and define query and recordset for Names list
    Set db = Access.CurrentDb
    Set qry = db.QueryDefs("qryStaffFullNamesAndOUCUs")
    Set rs = qry.OpenRecordset
        
    With shTmpStaff
        .Cells(1, 1).Value = "Name"
        .Cells(1, 2).Value = "OUCU"
        .Cells(1, 1).Resize(1, 2).Font.Bold = True
        
        Set rngRecordSet = .Cells(2, 1)
        rngRecordSet.CopyFromRecordset rs
        .Columns.AutoFit
    End With 'shTempStaff
    
    rs.Close
    qry.Close
    
    wbTemp.Names.Add Name:="Staff", RefersTo:=shTmpStaff.Cells(1, 1).CurrentRegion
    
    With shTmpData
        .Cells(1, 1).Value = "Date"
        .Cells(1, 2).Value = "Name"
        .Cells(1, 3).Value = "OUCU"
        .Cells(1, 4).Value = "Activity"
        .Cells(1, 5).Value = "Hours"
        .Cells(1, 1).Resize(1, 5).Font.Bold = True
        
        'Loop through day worksheets
        xlTemp.Calculation = xlCalculationManual
        For Each sh In _
            wbWorkflow.Sheets(Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"))
            
            For a = 1 To intNumberOfActivities
            
                'Find next available row on Data sheet
                lngNextRow = .Cells(xlTemp.Rows.Count, 1).End(xlUp).Row + 1
                
                'Copy date from date cell to Date column (1)
                With .Cells(lngNextRow, 1).Resize(intNumberOfDataRows, 1)
                    .Value = sh.Cells(intDateRow, intDateColumn).Value
                    .NumberFormat = "d mmm"
                End With
                
                'Copy Names (2)
                .Cells(lngNextRow, 2).Resize(intNumberOfDataRows, 1).Value = _
                    sh.Cells(intFirstDataRow, intNameColumn).Resize(intNumberOfDataRows, 1).Value
                    
                'Add formula for OUCU and then paste values (3)
                With .Cells(lngNextRow, 3).Resize(intNumberOfDataRows, 1)
                    .FormulaR1C1 = "=VLOOKUP(RC[-1],Staff,2,FALSE)"
                    '.Copy
                    '.PasteSpecial xlPasteValues
                End With
                
                'Copy Activity name (4)
                .Cells(lngNextRow, 4).Resize(intNumberOfDataRows, 1).Value = _
                    sh.Cells(intFirstDataRow - 1, intFirstCalcDataColumn + a - 1).Value
                    
                'Copy Hours (5)
                .Cells(lngNextRow, 5).Resize(intNumberOfDataRows, 1).Value = _
                    sh.Cells(intFirstDataRow, intFirstCalcDataColumn + a - 1) _
                    .Resize(intNumberOfDataRows, 1).Value
                    
            Next a
            
        Next sh
                     
        'Remove all rows where Hours=0
        .Cells(1, 1).CurrentRegion.AutoFilter Field:=5, Criteria1:="0"
        .Cells(1, 1).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData

        'Remove all rows where OUCU=#N/A
        .Cells(1, 1).CurrentRegion.AutoFilter Field:=3, Criteria1:="#N/A"
        .Cells(1, 1).CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData

        .Cells(1, 1).CurrentRegion.AutoFilter   'Removes AutoFilter
        
        With .Cells.Columns(3).EntireColumn
            .Copy
            .PasteSpecial xlPasteValues
        End With
        
        .Cells.Columns(2).EntireColumn.Delete
        .Cells.Columns.AutoFit
        
        xlTemp.Calculation = xlCalculationAutomatic
        
    End With 'shTempData
    
    With DoCmd
        .SetWarnings False
        .OpenQuery "qdelAllTempStaffWorkflowActivity"
        .TransferSpreadsheet _
            acImport, acSpreadsheetTypeExcel9, "TempStaffWorkflowActivity", _
            strTempFilename, True, "Data!"
        .OpenQuery "qupdTempStaffWorkflowActivity"
        .OpenQuery "qappUnmatchedTempStaffWorkflowActivity"
        .OpenQuery "qdelAllTempStaffWorkflowActivity"
        .SetWarnings True
    End With 'DoCmd
        
Exit_cmdImportWorkflow_Click:
    On Error Resume Next
    'Clean up and exit
    If Not wbTemp Is Nothing Then wbTemp.Close SaveChanges:=False
    If Not wbWorkflow Is Nothing Then wbWorkflow.Close SaveChanges:=False
    With xlTemp
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .Quit
    End With
    Set fld = Nothing
    Set rngRecordSet = Nothing
    Set shTmpData = Nothing
    Set shTmpStaff = Nothing
    Set rs = Nothing
    Set qry = Nothing
    Set db = Nothing
    Set wbTemp = Nothing
    Set wbWorkflow = Nothing
    Set xlTemp = Nothing
    Set dlgChooseWorkflow = Nothing
    
    Call KillExcel  'This needs to be removed when I can find what is keeping Excel alive
        
    Exit Sub
    
Err_cmdImportWorkflow_Click:
    'Error handling goes here
    MsgBox err.Description
    Resume Exit_cmdImportWorkflow_Click

End Sub

Also, as long as the 'old' Excel process is alive (if I don't kill it manually) I am getting an error to do with the range name in the TransferSpreadsheet call. This won't be an issue if I can kill the process properly, but I'm interested in the cause if anyone can help.

Thanks.