Closed workbook remains in memory.

[Kent Prokopy]

Excel 2000 SP3

I have an add-in I wrote, (One of many) myaddin.xla
For a simple breakdown, The add-in opens excel files, updates them via ADO
2.7. Closes and saves the files.

The add-in is called from an application called AutoMate. Using the
following code.
Private Sub Main
Dim xlApp As New Excel.Application
xlApp.Workbooks.Open "C:\myaddin.xla"
xlApp.Visible = True
xlApp.Run "MyModuleName"
xlApp.Quit
Set xlApp = Nothing
End Sub

This add-in updates 200+ workbooks. The problem I am having is that it is
running out of memory about half way through.
I am seeing something strange that may be someone else has seen and can give
me some advise on.

After the add-in opens, updates and closes the workbook. The workbook is
still listed in the VBAProject window. Still in memory.
It is closing the Excel Workbooks but not destroying them. I am using the
following syntax.

xlApp.ActiveWorkbook.Close SaveChanges:= True

Thank you in advance for any thoughts and or help.
Kent Prokopy

 

[Stephen Bullen]

Hi Kent,

After the add-in opens, updates and closes the workbook. The workbook is
still listed in the VBAProject window. Still in memory.
It is closing the Excel Workbooks but not destroying them. I am using the
following syntax.

This is symptomatic of your VB code continuing to have a reference to the
workbook in a variable somewhere.

Using the following code.
Private Sub Main
Dim xlApp As New Excel.Application
xlApp.Workbooks.Open "C:\myaddin.xla"
xlApp.Visible = True
xlApp.Run "MyModuleName"
xlApp.Quit
Set xlApp = Nothing
End Sub

You don't explicitly close the workbook here, so Excel may be waiting for a
'Save Changes' prompt. How about:

Private Sub Main
Dim xlApp As Excel.Application
Dim xlWkBk As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWkBk = xlApp.Workbooks.Open("C:\myaddin.xla")
xlApp.Visible = True
xlApp.Run "MyModuleName"

xlWkBk.Close False
Set xlWkBk = Nothing

xlApp.Quit
Set xlApp = Nothing
End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[Kent Prokopy]

Stephen

Let me try this again. The section you commented on is the part that calls
my add-in. The Add-In is the problem.

In the Add-In

Loop through the 200+ workbooks

Workbooks.Open "FilePathAndName.xls"

Populate with some data from ADO calls...This populates up to 100 sheets
of data in each workbook.

ActiveWorkbook.Close SaveChanges:=True ' This line should close
and destroy the workbook. It is not...

End Loop

 

[Stephen Bullen]

Hi Kent,

Workbooks.Open "FilePathAndName.xls"

Instead of relying on the ActiveWorkbook, I prefer to use a direct reference:

Set oBook = Workbooks.Open(...)

'Do Stuff

oBook.Close SaveChanges:=True

Populate with some data from ADO calls...This populates up to 100 sheets
of data in each workbook.

This is where I'd imagine your problem lies, with a reference leaking out. If
you comment out those lines, does the rest work fine?

Alternatively, are there any other addins that might be hooking a
workbook_open event?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[onedaywhen]

Just a thought: are you using ADO to query an open workbook, perhaps
using Jet's SELECT..INTO syntax targeting the active workbook, thus
falling foul of the ADO memory leak bug (MSDN Q319998)?

--

 

[Kent Prokopy]

Yes I am using ADO. But not on an open excel file.

Stephen, I am going to try your suggestion below. Thank you.

 

[onedaywhen]

Another thought: if your data source isn't an open workbook, is it
then a closed Excel workbook, MS Access or SQL Server database e.g.
you're using the MS OLDDB provider for Jet?

If so you may be able to use the SELECT..INTO or INSERT INTO..SELECT
with the IN keyword (both are MS proprietary syntax) to update all
your workbooks without having to open them. Not only would it avoid
the problem of the workbook remaining open (because you never opened
it!) it also happens to be faster.

BTW this morning I had the problem you described i.e. vba project
remains in the VBE after the workbook was closed. Cause? I'd
inadvertently been querying an open workbook!

--