[AccessD] Correctly Closing Excel

Hi Thomas

Declare your cell objects as Range and use Worksheet for Sheet. 
Set all object to Nothing before exiting the sub.

Browse the archive for numerous examples on handling Excel from within Access VBA.

/gustav

>>> ewaldt at gdls.com 08-08-2007 17:30 >>>
In running the code below, I find that Excel does not completely exit, as 
evidenced by Task Manager/Processes. 

I've either neglected something, or I've done something out of order. 
Could someone tell me my mistake, please?

Thanks. I've been struggling with this for a while now.

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems

---------------------------------------------------------------
Sub PrepData()

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")

'Open Excel workbook

    Set xlWB = xlApp.Workbooks.Open(strFileName) 'opens file
    DoCmd.SetWarnings False

'Prep Excel workbook
    PrepExcel

'Empty Clipboard before closing workbook
    xlApp.CutCopyMode = False

'Shut down Excel
    xlWB.Close savechanges:=True
    Set xlWB = Nothing
 
    xlApp.Quit
    Set xlApp = Nothing
 
    DoCmd.SetWarnings True

End Sub
---------------------------------------------------------------------
In case it's the PrepExcel sub that's causing the problem, here it is:
---------------------------------------------------------------------

Sub PrepExcel()
    Dim intNewRow As Integer
    Dim oCell As Object 
 
    Sheets.Add
    Sheets("Sheet1").Name = "ToImport"
    Sheets("ToImport").Select
 
'Copy only rows where column B = "A"
    intNewRow = 1

    For Each oCell In Sheets("GDLS-SHC").Range("B1:B200")
        If oCell.Formula = "A" Then
            oCell.EntireRow.Copy
            ActiveSheet.Paste 
Destination:=Worksheets("ToImport").Range("A" & intNewRow)
            intNewRow = intNewRow + 1
        End If
    Next oCell
 
    For Each oCell In Sheets("GDLS-C").Range("B1:B200")
        If oCell.Formula = "A" Then
            oCell.EntireRow.Copy
            ActiveSheet.Paste 
Destination:=Worksheets("ToImport").Range("A" & intNewRow)
            intNewRow = intNewRow + 1
        End If
    Next oCell
 
'Check for non-numeric in Pounds and Grams
    intNewRow = intNewRow - 1
 
    For Each oCell In Sheets("ToImport").Range("C1:D" & intNewRow)
        If Not IsNumeric(oCell.Formula) Then
            oCell.Formula = 0
        End If
    Next oCell
 
    For Each oCell In Sheets("ToImport").Range("C1:C" & intNewRow)
        oCell.Formula = oCell.Formula + Range("D" & oCell.Row).Formula * 
2.205 / 1000
    Next oCell

End Sub

----------------------------------------------------------

Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems