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.