Excel 2007 SP1 - Worksheet.Close does not release memory!

[Ron West]

I have been given a spreadsheet to fix, that worked fine using XL2000 but
runs out of memory halfway through when run using XL2007 SP1.

It has to create about 700 different account spreadsheets based on a single
template.

For each iteration, the template workbook is loaded and set to be a local
object wbOutput. It is then saved as the appropriate account name using
wbOutput.SaveAs; next, the different dataranges within are given their
values, etc, then wbOutput is re-saved and closed using wbOutput.Save
followed by wbOutput.Close.

wbOutput is only set to Nothing at the end of the procedure after all the
iterations, as that was sufficient in XL2000.

Using the GetProcessMemoryInfo() API, I have worked out that each time the
wbOutput.SaveAs method is executed, it grabs about 3Mb extra - but
wbOutput.Close does not release the memory as it did in XL2000 - so once
XL2007 gets to about the 500th iteration it crashes with insufficient memory.

I'm going to try setting wbOutput to Nothing in each iteration, to see if
that helps - but I still think this is a bug in XL2007 SP1 that needs fixing,
so if any MVP agrees with me and can report it to the appropriate place, I'd
appreciate it.

 

[Ron West]

I've just seen that wbOutput was already set to Nothing in each iteration!

So now I've got no idea how to get that memory released...

Help!

 

[Ron West]

I've tried re-running the spreadsheet under Excel 2000 (using Windows 2000).

Under Excel 2000, using the GetProcessMemoryInfo() API after each line shows
plainly that "Set wbOutput = Workbooks.Open( ... )" consumes about 2Mb and
the matching wbOutput.Close releases 2Mb for each iteration. Over a set of 40
iterations, the total consumption by Workbooks.Open was 77.492Mb and the
total memory-release by wbOutput.Close was -75.808Mb which near-enough
balances out given that other lines of code in the project are consuming and
releasing as well.

However, under Excel 2007 SP1 (using Windows XP), the Set wbOutput =
Workbooks.Open( ... )" line consumes about 3Mb and the matching
wbOutput.Close does NOT release any memory at all! This leads to the Excel
crash after about 470 iterations when all the available working memory has
been consumed.

Can some kindly MVP please report this to the Excel Development Team in time
to get it fixed for SP2?

Thanks,
Ron West
_____________________

 

[Ron West]

Ooops, sorry - as I had originally said in the 1st post - it's the
wbOutput.SaveAs method that grabs the 3Mb extra under XL2007 SP1.