3

In production vba, when opening excel file in a specific folder in loop I saw excel devours memory and finally causes a memory leak. I wrote short codes to investigate its root cause and facing the following questions.

Question.1

In loop it just opens and closes workbook but excel's memory usage increases. It seems some object is not released but I don't understand (a) which one is and (b) what's the problem of this code.

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

Question.2

I found that a use of COM object can be a solution. Indeed it starts another process and its memory usage never shows weird increase but I don't understand its reason because codes in loop are almost identical. Why this code doesn't lead a memory leak?

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

    Dim oxl As Excel.Application
    Set oxl = CreateObject("Excel.Application")
    oxl.Visible = False

    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 = oxl.Workbooks.Open(f.Path)
        wb.Close savechanges:=False
        Set wb = Nothing
    Next
    oxl.Application.Quit
    Set oxl = Nothing

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

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

Detailed response to @braX

I think you answered to Question.2 and intended a code like this. I understand that storing oxl.Workbooks improves performance than calling oxl.Workbooks every time, but in a perspective of memory usage it seems like a different story because Workbooks should be released out of the loop.

    Dim wbs as Workbooks
    Set wbs = oxl.Workbooks
    Dim Files As Variant
    Set Files = fso.GetFolder("*****").Files
    For Each f In Files
        Set wb = wbs.Open(f.Path)
        wb.Close savechanges:=False
        Set wb = Nothing
    Next
    Set wbs = Nothing
    oxl.Application.Quit
    Set oxl = Nothing
Takuya HARA
  • 499
  • 1
  • 3
  • 17
  • 1
    Well if there's a memory leak, doesn't it make complete and total sense that nuking the host process at every iteration *doesn't* leak any memory? – Mathieu Guindon Sep 21 '19 at 16:26
  • 1
    You arent setting the `Workbooks` collection to an object that you can set to `Nothing` when you are done with it. Try adding that in and killing it off at the right time as well. – braX Sep 21 '19 at 17:07
  • @MathieuGuindon I'm afraid not being able to understand your comment. I hopefully would like you to use more plain expression. – Takuya HARA Sep 22 '19 at 17:45
  • @braX Thank you for letting me know that `Workbooks` is an object that can be released. But the issue still exists. I think `Workbooks` should be released when loop is done. – Takuya HARA Sep 22 '19 at 17:52
  • Have a look at this. It may help... https://stackoverflow.com/questions/10309365/the-proper-way-to-dispose-excel-com-object-using-vb-net (although it is for .NET, the principles are similar) – braX Sep 22 '19 at 18:18
  • There is also a method (found somewhere here on stackoverflow) that lets you create a list of current Excel processes when you start, and then when the code is done, it will kill the Excel processes that are not in that list. – braX Sep 22 '19 at 18:21
  • @Mathieu re _that nuking the host process at every iteration doesn't leak_ but they aren't. It's nukes outside the For loop. (But that may hide the leak) – chris neilsen Sep 22 '19 at 18:28
  • @takuya are you sure example 2 hasn't leaked before `oxl.Application.Quit`? (Put a break on that line and check memory use before executing it) – chris neilsen Sep 22 '19 at 18:35
  • @chrisneilsen Exactly, thank you for pointing it out! I corrected my question. – Takuya HARA Sep 22 '19 at 18:35
  • @chrisneilsen It's just a typo, sorry for confusing. – Takuya HARA Sep 22 '19 at 18:40
  • @chrisneilsen In example 2 there's no leak. As it takes time to complete I sometimes observe its memory usage. – Takuya HARA Sep 22 '19 at 18:43
  • @Takuya FWIW, I can confirm I have reproduced your observations - Test1 memory increases over time, Test2 does not apear to (I say _apear_ as memory does fluctuate, but I don't observe a rising trend). I can't offer any explanation... – chris neilsen Sep 22 '19 at 22:01

0 Answers0