Docmd.Transferspreadsheet mystery

[phu81]

I've written Access VBA code to import a large number of Excel spreadsheets of a given format to my Access database. I've managed to create a file-browse functionality so I can find the spreadsheet to import. The VBA creates an instance of Excel.Application, opens the selected workbook then picks out the named range in that workbook to import. It then calls Docmd.Transferspreadsheet to do the actual data transfer. It's all working but for one annoying problem: although my code closes the workbook and quits the Excel application, I can't end the EXCEL.EXE process which always remains in Windows task manager at end - this causes major problems for subsequent imports.

Here's what my code looks like

'Create Excel application and open chosen workbook
Dim xlApp As Excel.Application = New Excel.Application
Dim xlWb As Excel.Workbook = xlApp.Workbooks.Open(myfilepath)

'Transfer range in question
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, mytable, filepath, True, myrange

'Close & cleanup
xlWb.close
xlApp.quit
set xlApp = nothing
set xlWb = nothing

I'm positive all code thru the transferspreadsheet call is working - for some reason it's simply not closing the workbook & quitting Excel properly.

Making things more bizarre, it somehow DOES work if I stick the 'xlWb.close' BEFORE the transferspreadsheet call - EXCEL.EXE no longer shows in task manager. But how can the transfer work if the workbook's closed first?

Would really appreciate some help. -Newbie

 

[RoyVidar]

Here's a thread recommending using the [tt]End[/tt] keyword to terminate the Excel session (thread705-673915).

Roy-Vidar