Open and close multiple workbooks - out of memory

[Per Olander]

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]

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]

"test" took 89 seconds whereas "M_snb" took 64 seconds on my machine.

[Kenneth Hobs]

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.