I'm using VBA in Access 2003 to open an Excel spreadsheet and dump data from two worksheets into two tables. The code does not work if Excel is already open, and once the code ends the Excel process is left running. To further complicate things even with just the process open the code does not work, so after each run I have to open the task manager and manually end the Excel process. Surely there is a way to close the process from within VBA, no?
Code:
Dim objExcel As Object, objWb As Object
...
strFile = "U:\Jim.ONeil\Franklin\June Original.xls"
strTable1 = "tblSummaryLanding"
strSheet1 = "Summary!"
strTable2 = "tblMonthlyLanding"
strSheet2 = "June2008!"
Set objExcel = CreateObject("Excel.Application")
With objExcel
Set objWb = .Workbooks.Open(FileName:=strFile, Password:="finance")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable1, strFile, 0, strSheet1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable2, strFile, 0, strSheet2
objWb.Close SaveChanges:=False
.Quit
Set objExcel = Nothing
End With