Close all Instances of Excel

[Alan]

I have a routine that imports data daily from various spreadsheets. It loops
through vaious departmentnames openining a different spreadsheet each time.
Due to naming conventions I have had to open and close the workbooks each
time I import. I have written the code below, however it seems to leave an
instance of excel open within Task Manager that eventaully uses considerable
system memory and causes the routine to halt
I dont seem to be able to close this instance down

Any Suggestions would be appreciated
MS Office 2003
Option Compare Database

Public Sub testimport()

Dim xlApp As Excel.Application
Dim xlBook As Excel.workbook

Set rstDEPTList = CurrentDb.OpenRecordset("SELECT Name from tblSQL where
DATASET='DepartmentNames'", dbOpenDynaset)
dtDeptDate = #7/1/2009#
strDEPTDate = Format(dtDEPTDate, "yyyy-mm-dd")
strDEPTSheetName = Format(dtDEPTDate, "dd-mm-yy")
dtDEPTNextDate = DateAdd("d", 1, dtDEPTDate)

DoCmd.SetWarnings False

While DIR("O:\ Daily Files\ALL_DEPTS_" & strDEPTDate & ".zip") <> ""


DoCmd.RunSQL ("DELETE * FROM tblDEPT_DutyImport")

'Call UnZipFile("O:\Daily Files\ALL_DEPTS_" & strDEPTDate & ".zip",
"O:\Daily Files\Performance", 10)

With rstDEPTList
..MoveFirst

While rstEPTList.EOF = False
Set xlApp = CreateObject("Excel.Application")

DoCmd.SetWarnings False
strFilePath = "O:\Daily Files\Performance\" & .Fields("Name") & "_" &
strDEPTDate & ".xls"

Set xlBook = xlApp.workbooks.Open(strFilePath)
DoCmd.TransferSpreadsheet acImport, , "tblDEPT_DutyImport", strFilePath,
True, strDEPTSheetName & "$"

xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

'Kill strFilePath
Debug.Print .Fields("Name") & " COMPLETE"

..MoveNext
Wend
End With
Set xlApp = Nothing
Set xlApp = Nothing

Wend
End Sub

 

[Paolo]

Hi Alan,
try in this way

xlApp.Quit
Set xlBook = Nothing

HTH Paolo

 

[Alan]

Paolo

Sorry No Joy
Still the same issues

 

[Klatuu]

Most of your code is unnecessary. You don't have to open an Excel file to
use the TransferSpreasheet method

 

[Alan]

Dave

Many Thanks for your reply
I have discovered an issue with the excel files I am trying to import from s
they are supplied in Windows 95 format and when I import all the data is
missing.
I have done some additional work and now open and save the files as 97-2003
which overcomes the problem

Unless you know of another way ???
There was an extensive thread before on importing from closed workbook but I
have now eventually found the issue