PDA

View Full Version : Open and close multiple workbooks - out of memory



Per Olander
09-25-2014, 01:07 AM
Hi,


I am doing a larger project, where a loop is opening/adding a lot of workbooks, performing some actions, saving and closing the workbooks again.
This runs as an endless loop on a server, and takes a few minutes per loop.


Whenever the procedure runs for a specific amount of time, the code fails on the same line, set WB = workbooks.add
the endless loop can run for a some days before encountering this problem, but a lot of co-workers are relying on the results that are constantly being generated, and I am not always available to reset everything when the error occurs.


I have been reading alot about forcing the variable to be be released, set WB = Nothing, everytime the file has been closed, but this small sample code keeps running out of memory at around i = 2324:



Sub Test()
Application.ScreenUpdating = False
Dim WB As Workbook
Dim i As Long
For i = 1 To 10000
Set WB = Workbooks.Add
WB.Close
Set WB = Nothing
Next i
End Sub


Are there any other ways to force Excel to free up memory, or should I just accept that my program needs to be reset once every few days?


Best Regards,
Per Olander

snb
09-25-2014, 02:05 AM
Please use code tags around VBA code.


Sub Test()
Application.ScreenUpdating = False

For j = 1 To 10000
with Workbooks.Add
.Close 0
end with
Next
End Sub

In a separate (hidden) instance of Excel:


Sub M_snb()
With CreateObject("Excel.application")
For j = 1 To 10000
With .Workbooks.Add
.Close 0
End With
Next
.Visible = True
End With
End Sub

mancubus
09-25-2014, 02:53 AM
"test" took 89 seconds whereas "M_snb" took 64 seconds on my machine.

Kenneth Hobs
09-25-2014, 06:45 AM
Hmm, my computer is way slower. For snb's methods I found 3:10 and 1:50.

The bigger issue is when the user leaves the application to browse the web for example. The foreground method will cause it to hang whereas the background method did not.

The lesson here is if you can live with the background method, it is best.