Macro to clear Memory

[howard]

From time to time I have a macro to open up several workbooks and to run a macro to update these workbooks


Unfortunately, it uses a lot of RAM and occasionally the workbooks freeze and I need to close down Excel and restart Excel and re-run the macro


I would like to know if there is VBA code to clear the RAM after the macro has done what it is supposed to do.


If so kindly provide me with code to do so. If not is the any commercial software or freeware that can clear unused RAM ?

 

[paulxmw]

you probably just need to empty the clipboard. add this code to the end of your macro

Dim DataObj As New MSForms.DataObject 'empty the clipboard
DataObj.SetText ""
DataObj.PutInClipboard

 

[howard]

Thanks for the code. will try this. I'm sure it will help

 

[hiker95]

From time to time I have a macro to open up several workbooks and to run a macro to update these workbooks

Unfortunately, it uses a lot of RAM and occasionally the workbooks freeze and I need to close down Excel and restart Excel and re-run the macro

I would like to know if there is VBA code to clear the RAM after the macro has done what it is supposed to do.

If so kindly provide me with code to do so. If not is the any commercial software or freeware that can clear unused RAM ?

howard,

Application.CutCopyMode = False

So that we can get it right on the first try, can we see all of your macro code?


When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[howard]

See My full code below

 Sub Update_Data()
List_Man_Acc_FileNames
Copy_ListFiles
Delete_Row
Open_Files
Export_TB
Dim DataObj As New MSForms.DataObject 'empty the clipboard
 DataObj.SetText ""
 DataObj.PutInClipboard

End Sub





Sub List_Man_Acc_FileNames()

Sheets("file names").Range("A1:C150").ClearContents
Application.ScreenUpdating = False
Sheets("file names").Range("A1:C1").Value = Array("File Name", "Created", "Last Modified")

LoopController ("C:\pull")
Sheets("file names").Columns.AutoFit

End Sub

Private Sub LoopController(sSourceFolder As String)
'This will loop into itself, first processing the files in the folder
'then looping into each subfolder deeper and deeper until all folders processed
Dim Fldr As Object, FL As Object, SubFldr As Object

    Call ListFilesinFolder(sSourceFolder & Application.PathSeparator)

    Set Fldr = CreateObject("Scripting.FileSystemObject").GetFolder(sSourceFolder)
    For Each SubFldr In Fldr.SubFolders
        LoopController SubFldr.path
    Next

End Sub

Sub ListFilesinFolder(MyPath As String)
Dim FSO As Object, f As Object, FLD As Object, NR As Long

NR = Sheets("file names").Range("A" & Rows.Count).End(xlUp).Row
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder(MyPath).Files

For Each f In FLD
    If InStr(f.Name, "ACCNTS(P)") > 0 And Right(f.Name, 4) = ".xls" Then
        NR = NR + 1
        Sheets("file names").Range("A" & NR).Value = f.Name
        Sheets("file names").Range("B" & NR).Value = f.DateCreated
        On Error Resume Next
        Sheets("file names").Range("C" & NR).Value = f.DateLastModified
        On Error GoTo 0
    End If
Next f

End Sub
Sub Copy_ListFiles()

With Sheets("Workspace")
.Range("A1:A50").ClearContents
End With


Dim LR As Long
    With Sheets("File names")
        LR = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A2:A" & LR).Copy
    End With
    With Sheets("Workspace")
        .Range("A1").PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

Sub Replace_Data()
Sheets("Workspace").Select
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:A" & LR)
.Replace What:=".xls", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With

 
 
End Sub



Sub Delete_Row()
With Sheets("Workspace")

Dim LR As Long, I As Long

LR = .Cells(Rows.Count, "A").End(xlUp).Row 'last row
    For I = LR To 1 Step -1



        If .Cells(I, "A") Like "[Bb][Rr]" Then Rows(I).Delete
        
    Next I
End With

End Sub

 

[hiker95]

howard,

I have examined your macros, and, can not suggest anything else that might help.

Maybe someone else on MrExcel will be able to help you.

 

[howard]

Thanks for getting back to me

I incorporated the following at the end of my code as kindly provided by Paul

 Dim DataObj As New MSForms.DataObject 'empty the clipboard
 DataObj.SetText ""
 DataObj.PutInClipboard

 

[hiker95]

Thanks for getting back to me

I incorporated the following at the end of my code as kindly provided by Paul

howard,

You are very welcome. Glad that Paul was able to solve your request.

And, come back anytime.

 

[howard]

Hi Paul


I have tested your code this morning


when running the macro, I get user defined type not defined and the code below is highlighted


please check and advise when I need to do

 Dim DataObj As New MSForms.DataObject 'empty the clipboard

 

[paulxmw]

have yu put it as the last thing the macro does as i use it and it works for me