Close all Instances of Excel



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

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 & "$"

Set xlBook = Nothing
Set xlApp = Nothing

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

End With
Set xlApp = Nothing
Set xlApp = Nothing

End Sub




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





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
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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question