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;
folder contains 3000 excel files so that loop is done 3000 times
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