Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm extracting data from a larger number of excel files.
For every opening and closing of a file the memory gets smaller and smaller even if i don't save any data from the file.
I noticed that a lot of VBAproject's stay in memory, even though I have closed the file. How do I close the file entirely and free the memory for the VBAproject's ?


Sub Main()
    Dim info, analyse As Variant
    Dim i, n As Integer
    Beep
    Dim colFiles As Collection, f, wb As Workbook
    
    Application.ScreenUpdating = False
    
    Set colFiles = GetMatches("c:\Users\Documents\Analyseenhed\LIMS_2022\", "*Resultat*.xlsx")
    n = colFiles.Count
    i = 0
    For Each f In colFiles
        Set wb = Workbooks.Open(f.path, UpdateLinks:=False, ReadOnly:=True)
        wb.Close
        WB=nothing
        Debug.Print i & " of " & n & "   " & GetMemory
        i = i + 1
    Next f
    
    
End Sub

Function GetMemory()
    Dim oInstance As Object, colInstances As Object
    Dim totalMem As Single, availMem As Single
 
    Set colInstances = GetObject("winmgmts:").ExecQuery("SELECT * FROM Win32_PhysicalMemory")
    For Each oInstance In colInstances
        totalMem = totalMem + oInstance.Capacity / 1024 / 1024
    Next


    Set colInstances = GetObject("winmgmts:").ExecQuery("SELECT * FROM Win32_OperatingSystem")
    For Each oInstance In colInstances
        availMem = availMem + oInstance.FreePhysicalMemory / 1024
    Next
    
    
    GetMemory = Round(availMem / totalMem * 100, 1)
    Set colInstances = Nothing
    Set oInstance = Nothing
End Function


What I have tried:

I closed the file and set the wb object to nothing
Posted
Updated 16-Jan-23 8:20am

If you are still seeing VBA Projects for the files you have opened then you are not actually closing them.
VB
wb.Close
WB=nothing
looks strange. WB should have automatically adjusted to wb and that second line should have thrown an error "Object Required". Try instead
VB
wb.Close
Set wb=nothing

Another alternative - instead of opening and closing the Workbooks why not try using a Power Query to extract (and combine) the data?
 
Share this answer
 
It seems that its a problem with no solution:


Redirecting[^]
 
Share this answer
 
Comments
CHill60 16-Jan-23 12:33pm    
There are several potential solutions listed on that link. Did you try them all?
Thank you for your info about the Power Query. It's new for me. I will certainly use it in future. Correct me if I'm wrong, but power query is more if you have the data in table form like in a database. The workbooks I'm extracting data from, are very unstructured and data are to be found spread out over the whole worksheet.
The
set wb = nothing or wb = nothing
didn't not make any change.
It's actually the same, if I open a workbook manually - the VBproject stays in memory after I closed the workbook. I can see it in the macro window
 
Share this answer
 
Comments
Richard Deeming 17-Jan-23 4:25am    
If you want to reply to a solution, click the "Have a Question or Comment?" button under that solution and post a comment.

Do not post your comment as another "solution" to your question.
Koppelgaard 17-Jan-23 10:46am    
Thank you for your info about the Power Query. It's new for me. I will certainly use it in future. Correct me if I'm wrong, but power query is more if you have the data in table form like in a database. The workbooks I'm extracting data from, are very unstructured and data are to be found spread out over the whole worksheet.
The
set wb = nothing or wb = nothing
didn't not make any change.
It's actually the same, if I open a workbook manually - the VBproject stays in memory after I closed the workbook. I can see it in the macro window
Improve solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900