Excel VBA Application Memory Usage Bloat 1

[corycrum]

I have a sub routine that opens and closes another Excel workbook as part of a loop - for say 100 iterations. I'm running into a problem with "bloat" in the Excel application. Closing the workbook doesn't seem to free-up the memory - i monitor it on the "process" tab of the Windows Task Manager.

How can i force Excel to free up this memory? I've tried "Set wkbWorkbook = Nothing" inside the loop after each wkbWorkbook.close statement but this doesn't seem to work.

Is there something i'm not thinking of? The simple routine below also creates this issue for me. Excel memory steadily increases until the system crashes.

Sub MakeExcelBloat()
Dim wkb As Workbook
Do Until 1 = 2
Set wkb = Workbooks.Open("C:\Big Ugly Excel File.xls")
wkb.Close
Loop
End Sub

 

[SkipVought]

hi,

opens and closes another Excel workbook as part of a loop - for say 100 iterations

WHY? Is opening & closing 100+ time really necessary? Could you not open/do loop/close?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[corycrum]

Thanks Skip - great question. I'm i'm hooking into Adobe's PDFMaker API to print the Excel workbooks in PDF format. Unfortunately, as part of the printing process, PDFMaker closes the workbook and then reopens it - destroying the object variable associated with the workbook. I then have to reopen the workbook for the next iteration.

Your next question might be... why not use Adobe distiller instead of PDFMaker? The PDFMaker printer preserves embadded hyperlinks within the Excel file and converts Excel cell comments to annotations in the PDF document - two features that are very important to me. Distiller does not have this functionality - i don't think.

 

[SkipVought]

Without seeeing code, there is little hope for help. And that might not help much either.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vbdbcoder]

Set wkb = Nothing

 

[corycrum]

Thanks vbdbcoder - i have tried "Set wkb = Nothing" inside of the loop and it doesn't seem to resolve the issue.

One thing to note - when the VBA editor is open, i can see a new VBAProject open each time a new workbook is opened. When the workbook closes, the VBAProject does not dissapear from the Project Explorer window. I don't know if this is a bug in Excel. I have never noticed this before. I can still access all of the worksheet/workbook/standard/class modules in each VBAProject even though the respective workbook is closed. This may be related to the bloat problem i'm having.

 

[SkipVought]

CODE? CODE? CODE?

How can I tell thee?

Let me count the ways.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[corycrum]

Skip - i can post the code tomorrow, but it's long and ugly with many calls to separate sub procedures.

Again - the problem exists with the simple code above. With each iteration, the Mem Usage grows...

Sub MakeExcelBloat()
Dim wkb As Workbook
Do Until 1 = 2
Set wkb = Workbooks.Open("C:\Big Ugly Excel File.xls")
wkb.Close
Loop
End Sub

 

[SkipVought]

I'd try this
]code]
Do Until 1 = 2
Set wkb = Workbooks.Open("C:\Documents and Settings\ii36250\My Documents\DSC MS Decel COMSPR.xlsm")
wkb.Close
Set wkb = Nothing
Loop
[/code]
BTW, I can run your loop code with no increase in memory usage.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarduspater]

Read this:

http://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm

Candu si tene su 'entu es prezisu bentolare.

 

[sarduspater]

Using Add method I think it would work:

Sub xx()
Dim w As Object
On Error GoTo err
Application.DisplayAlerts = False
Do
Set w = Workbooks.Add("obj.xls")
' ....
w.SaveAs Filename:="obj.xls"
w.Close
Loop
Exit Sub
err:
Debug.Print err.Number, err.Description
Resume Next
End Sub


Candu si tene su 'entu es prezisu bentolare.