0

forked from Workbooks.Open causes memory leak

Background

I'm investigating a cause of memory leak happened on the following code and found that it happens when excel doesn't respond. After a while running this code I see excel's process is 'not responding' on task manager and process starts devouring memory.

Sub Test01()
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim f As File
    Dim wb As Workbook

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Dim Files As Variant
    Set Files = fso.GetFolder("*****").Files
    For Each f In Files
        Set wb = Workbooks.Open(f.Path)
        wb.Close savechanges:=False
        Set wb = Nothing
    Next

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

When I add DoEvents in loop excel never freezes and memory usage increases slowly.

    For Each f In Files
        DoEvents
        Set wb = Workbooks.Open(f.Path)
        wb.Close savechanges:=False
        Set wb = Nothing
    Next

Question

Why memory usage increases when process is freezing?

Remarks

Please note that;

  1. folder contains 3000 excel files so that loop is done 3000 times

  2. file size of excel files is less than 15kb

Takuya HARA
  • 499
  • 1
  • 3
  • 17
  • Seems you’re in the same spot as this guy here: https://www.experts-exchange.com/questions/29017231/Excel-2010-Not-releasing-memory-when-workbooks-closed.html. They mention the use of arrays of arrays and that could cause the memory flaw. – Ricardo Diaz Sep 22 '19 at 20:18
  • Maybe because you are trying to open a file when the path hasn't been defined. This line `Set = Workbooks.Open(fPath)` should have errors cause nowhere have you defined `fPath`. If you would have used `Option Explicit` or `Compile`.......... – alowflyingpig Sep 23 '19 at 00:51
  • @alowflyingpig that was a typo OP repeated from their other post. I can [confirm](https://stackoverflow.com/questions/58041920/workbooks-open-causes-memory-leak#comment102505344_58041920) the issue the OP describes is real. – chris neilsen Sep 23 '19 at 01:05
  • @alowflyingpig Sorry I corrected my question as it's just a typo. – Takuya HARA Sep 23 '19 at 01:51
  • @chris You're exactly right, thank you for taking a look at this too. – Takuya HARA Sep 23 '19 at 01:52

0 Answers0