Excel Won't unload from Memory

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

My Access 2002 app uses the following code to open Excel
and loop thru a wrokbooks worksheets. The code works but
the Excel won't come out of memory (Task Mgr). What am I
missing?

Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open (strFile)

For Each Worksheet In objExcel.Worksheets
Dim db As DAO.Database, t As DAO.TableDef, i As Integer
Set db = CurrentDb()
For i = db.TableDefs.Count - 1 To 0 Step -1
Set t = db.TableDefs(i)
If t.Name = Worksheet.Name Then
db.TableDefs.Delete t.Name
End If
Next i
db.Close
rstTARGET.AddNew
DoCmd.Echo True, "Processing Import of " & Worksheet.Name
DoCmd.TransferSpreadsheet acImport, 8, Worksheet.Name,
strFile, False, Worksheet.Name & "!" & "A1:AH100"
rstTARGET!TBL_NAME = Worksheet.Name
rstTARGET.Update
Next Worksheet
objExcel.Application.ActiveWorkbook.Save
objExcel.Quit
Set objExcel = Nothing
 
If your code is late bound - which it looks to be, since you dim objExcel as
object, not as Excel.Application - you must qualify all your Excel objects
with a full path back to objExcel.

So:
for each Worksheet in objExcel.worksheets

or:

... = objExcel.worksheets("myworksheet")...

etc.

HTH,
TC
 
For Each Worksheet In objExcel.Worksheets
Click to expand...

Since you are apparently not using Option Explicit (which is Highly
Recommended), then this line creates a Variant that will contain a handle
to a worksheet in the Excel.Application.

Nowhere do you drop this handle (with, for example

Set Worksheet = Nothing

or even

Worsheet = 31.7 / 4 * Sin(1)

), then the reference-count for the application object never gets to zero
and it will never go out of memory. In any case, Worksheets is a method of
the Workbook object, not the Application object so there are all kinds of
implicit default-property shortcuts going on, that are likely to be setting
up undestroyable references too, and they will prevent Excel unloading too.

Hope that helps


Tim F
 
I added an option explicit and dim objwks as worksheet,
and closed it. What in the heck am I missing!!!!!! It's
still in memory after I close.


Dim objExcel As Excel.Application

Dim objWks As Excel.Worksheet

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open (strFile)

For Each objWks In
objExcel.ActiveWorkbook.Worksheets
Dim db As DAO.Database, t As DAO.TableDef, i As
Integer
Set db = CurrentDb()

For i = db.TableDefs.Count - 1 To 0 Step -1

Set t = db.TableDefs(i)

If t.Name = objWks.Name Then

db.TableDefs.Delete t.Name

End If

Next i

db.Close

rstTARGET.AddNew

DoCmd.Echo True, "Processing Import of " &
objWks.Name

DoCmd.TransferSpreadsheet acImport, 8,
objWks.Name, strFile, False, objWks.Name & "!"
& "A1:AH100"

rstTARGET!TBL_NAME = objWks.Name

rstTARGET.Update

Next objWks

objExcel.Application.ActiveWorkbook.Save

objExcel.Quit

Set objWks = Nothing

Set objExcel = Nothing
 
I added an option explicit and dim objwks as worksheet,
and closed it. What in the heck am I missing!!!!!! It's
still in memory after I close.
Click to expand...

Comments in line (shame there aren't any in the code!!)

' I see you are using early binding: which is fine and makes the
' whole thing run quicker ...
Dim objExcel As Excel.Application
Dim objWks As Excel.Worksheet
Click to expand...

'
' ... but then you go and spoil it all by using a CreateObject. I
' am slightly surprised that this compiles or runs, but it clearly
' does. I would still use a New to create the application rather than
' this. I have no idea if it's what is causing your problem though
'
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open (strFile)
Click to expand...

' This reference to ActiveWorkbook _may_ be okay, but it's
' clumsy. Use the actual workbook object returned by the Open
' method above.
For Each objWks In objExcel.ActiveWorkbook.Worksheets
Click to expand...

' It's not illegal, but pretty dumb to put Dims inside a loop
Dim db As DAO.Database
Dim t As DAO.TableDef
Dim i As Integer
Click to expand...

' and even less smart to do this inside a loop. Move it to
' the top

' etc, etc,
Next objWks
Click to expand...
' I am not completely sure if the For Each leaves the variable
' nothinged or not. You could try something like

If objWks Is Nothing Then MsgBox "Look, there's no worksheet"

' to test.

' Once again, I would use the workbook rather than relying
' on the vagaries of the Excel GUI.
objExcel.Application.ActiveWorkbook.Save
Click to expand...

' At this stage, you still have a reference to the worksheet,
' you should have one to the workbook. I would be dropping the
' sheet here, and closing the workbook and then dropping that
' handle
objExcel.Quit

Set objWks = Nothing
Set objExcel = Nothing
Click to expand...


As above, I don't know if it will cure the problem. OLE is funny technology
and MS do not always follow their own rules. I generally find it easier to
program database/ spreadsheet stuff within Excel itself because it's so
much quicker to debug, and you get much better methods and properties to
call.

Hope that helps


Tim F
 
Tim Ferguson said:
(snip)

' I see you are using early binding: which is fine and makes the
' whole thing run quicker ...
Click to expand...
' ... but then you go and spoil it all by using a CreateObject. I
' am slightly surprised that this compiles or runs, but it clearly
' does.
Click to expand...

MS explicitly recommend using createobject in both early & late bound
scenarios: Q244464: "When creating an instance of an Microsoft Office
application, use CreateObject instead of New. CreateObject more closely maps
to the creation process used by most Visual C++ clients, and allows for
possible changes in the server's CLSID between versions. CreateObject can be
used with both early-bound and late-bound objects."

If the OP did what I suggested, his problem would be solved :-)

Cheers,
TC
 
TC said:
MS explicitly recommend using createobject in both early & late bound
scenarios:
Click to expand...

Thanks for that: I did not know.
If the OP did what I suggested, his problem would be solved :-)
Click to expand...

I may have misread, but I think he has, hasn't he?

All the best


Tim F
 
By moving the DoCmd.TransferSpreadsheet outside the Excel
Automation Loop it worked as expected. No Excel left in
memory and a dramtic performance increase.

Thanks to everyone...




For Each objWks In
objExcel.ActiveWorkbook.Worksheets
Set db = CurrentDb()
For i = db.TableDefs.Count - 1 To 0 Step -1
Set t = db.TableDefs(i)
If t.Name = objWks.Name Then
db.TableDefs.Delete t.Name
End If
Next i
db.Close
rstTARGET.AddNew
DoCmd.Echo True, "Processing Import of " &
objWks.Name
'DoCmd.TransferSpreadsheet acImport, 8,
objWks.Name, strFile, False, objWks.Name & "!"
& "A1:AH100"
rstTARGET!tbl_name = objWks.Name
rstTARGET.Update
Next objWks
objExcel.Application.ActiveWorkbook.Save
objExcel.Quit
Set objExcel = Nothing

'*********************************************************
**********
'NEW LOOP
rstTARGET.MoveFirst
Do Until rstTARGET.EOF
DoCmd.TransferSpreadsheet acImport, 8, rstTARGET!
tbl_name, strFile, False, rstTARGET!tbl_name & "!"
& "A1:AH100"
rstTARGET.MoveNext
Loop
 
He dim'd objWsheet (or whatever he called it) As Worksheet. This still
establishes a hidden reference to Excel. I suggested that he dim it As
Object.

Cheers :-)
TC
 
Back
Top