[AccessD] Correctly Closing Excel

ewaldt at gdls.com ewaldt at gdls.com
Wed Aug 8 10:30:20 CDT 2007


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





This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.




More information about the AccessD mailing list