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
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