I ran a simple experiment. I wrote a macro to create 150 xls files and
another macro to read 150 macro files. I recorded the memory usages before
and after the code ran. I didn't see any significant lose of memory. There
was only minor changes. Below are my results and the two macros I wrote. I
don't think the problem is with opening and closing the workbooks. I suspect
the problem is with the Master Worksheet growing in size. I suspect if the
Master Workbook is closed most of the memory will be recovered.

Original

Physical (K)
total = 384536
Available = 59000
Cache = 109068

Kernel (K)
total = 81840
Paged = 75008
NonPaged = 6832

After Writes

Physical (K)
total = 384536
Available = 57428
Cache = 130532

Kernel (K)
total = 84028
Paged = 77004
NonPaged = 7024

After Reads
Physical (K)
total = 384536
Available = 55248
Cache = 134868

Kernel (K)
total = 84996
Paged = 77860
NonPaged = 7136




Sub MakeBooks()

Folder = "C:\temp\test2\"

For i = 1 To 150
With ThisWorkbook
.Sheets("sheet1").Copy
ActiveWorkbook.SaveAs Filename:=Folder & "Test" & i & ".xls"
ActiveWorkbook.Close
End With

Next i


End Sub


Sub ReadBooks()

Folder = "C:\temp\test2\"

FName = Dir(Folder & "*.xls")
RowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
With ThisWorkbook.Sheets("Sheet1")
OldBk.Sheets("Sheet1").Rows(1).Copy _
Destination:=.Rows(RowCount)
End With
RowCount = RowCount + 1
OldBk.Close
FName = Dir()
Loop

End Sub


"JP" wrote:

But why are we even creating a new instance of the Excel application
at all? This code is running natively inside Excel (if I assume
correctly).

--JP


On Sep 13, 10:26 am, Joel wrote:
I believe this is a memory leak

Set xl = Nothing

You aren't releasing any memory. All you are doing is setting the variable
xl to nothing. The memory is still allocated to the program.

Try this

Sub MyMacro()
Dim xl
Set xl = CreateObject("word.application")
xl.Visible = True
Set xl = Nothing

End Sub

The word document stays opened. Close the word document manually. If the
object was invisiable it would still be running. You can prove this by not
making the object visible and creating the object in the code above. Then go
to Task Manager. You will still see Word running. Just because you cna't
see it running doesn't mean that it is NOT running.

Now this

Sub MyMacro()
Dim xl
Set xl = CreateObject("word.application")
xl.Visible = True

xl.Quit

End Sub