Close all Instances of Excel


A

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
 
Ad

Advertisements

K

Klatuu

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

Advertisements

A

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
 

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

Top