Clearing memory when processing files in Excel

[jrisebo]

We have a process where we have many data files (txt files) that excel will open, read a bit of data, and then move on to the next one. It seems to bomb out based on what I think is a memory error.

Is there a way to clear the memory the computer uses in the code, i.e. it opens the file, reads the text, pastes it where we want it, and then clears its memory for the next file?

Thanks

 

[Logit]

This posting has a suggestion : Macro to clear Memory

Here are some other suggestions :

If you have used a 'copy / paste' somewhere in your project .. at the end of that macro make sure you clear the clipboard with :

Application.CutCopyMode = False

You can save the workbook periodically to clear up some memory as well :

ThisWorkbook.Save

You can set objects to Nothing :

Sub Whatever()
Dim someLargeObject as SomeObject

'expensive computation

Set someLargeObject = Nothing
End Sub

Here are some MS tips for creating a project that uses less memory :

https://support.microsoft.com/en-us/...es-less-memory



And here are some more suggestions, some of which I personally disagree ... like turning off "AutoRecovery". For some folks that could be a disaster.

https://www.add-ins.com/support/out-...soft-excel.htm

 

[jrisebo]

The code doesnt specifically use cut/copy/paste, it looks for the value in the text file and I assume pastes the value, would that be the same thing?

Here is the code

Sub REACTIONS()
'
'
   

'   Name current file
    MyMacroFile = ActiveWorkbook.Name
    LiveLoad = Range("LL")
    DeadLoad = Range("DL")
    Archload = 1
    spans = Range("spancount")
    begin1 = Range("begin_span1")
    begin2 = Range("begin_span2")
    begin3 = Range("begin_span3")
    begin4 = Range("begin_span4")
    begin5 = Range("begin_span5")
    begin6 = Range("begin_span6")
    begin7 = Range("begin_span7")
    begin8 = Range("begin_span8")
    begin9 = Range("begin_span9")
    begin10 = Range("begin_span10")
    end1 = Range("end_span1")
    end2 = Range("end_span2")
    end3 = Range("end_span3")
    end4 = Range("end_span4")
    end5 = Range("end_span5")
    end6 = Range("end_span6")
    end7 = Range("end_span7")
    end8 = Range("end_span8")
    end9 = Range("end_span9")
    end10 = Range("end_span10")
    filecount = Range("filecount") + 8
    Application.ScreenUpdating = False

    'save workbook before macro runs
    ActiveWorkbook.Save
    'clear all data from cells
    Range("J9:M28000").ClearContents
            

    mydir = Range("mydir")
    mypath = mydir
    
    For i = 9 To filecount
        j = Cells(i, 9)
        k = Cells(i, 8)
    
        If j = 0 Then
            Exit For
            End If
                                             
        MyFile = mypath & "\" & j
   
'   Open .PRT file
    Workbooks.OpenText Filename:=MyFile, Origin:=xlWindows, StartRow:=1, _
        DataType:=xlFixedWidth, FieldInfo:=Array(0, 2)

'   Name text file
        MyTextFile = ActiveWorkbook.Name
        Windows(MyTextFile).Activate


 
 'GET THRUST VALUES
      'FsInner
        Cells.Find(What:="STRESSES IN CULVERT WALL  (PSI)  FOR LOAD INCREMENT" & Right("      " & LiveLoad, 6), After:=ActiveCell, LookIn _
            :=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
            xlNext, MatchCase:=False).Activate
        posbegin1 = Left(Right(ActiveCell.Offset(6 + k, 0), 55), 10)
        
      'FsOuter
        Cells.Find(What:="STRESSES IN CULVERT WALL  (PSI)  FOR LOAD INCREMENT" & Right("      " & LiveLoad, 6), After:=ActiveCell, LookIn _
            :=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
            xlNext, MatchCase:=False).Activate
        negbegin1 = Left(Right(ActiveCell.Offset(6 + k, 0), 40), 10)
        
      'FsComp
        Cells.Find(What:="STRESSES IN CULVERT WALL  (PSI)  FOR LOAD INCREMENT" & Right("      " & LiveLoad, 6), After:=ActiveCell, LookIn _
            :=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
            xlNext, MatchCase:=False).Activate
        compbegin1 = Left(Right(ActiveCell.Offset(6 + k, 0), 25), 10)
        
      'Fv
        Cells.Find(What:="STRESSES IN CULVERT WALL  (PSI)  FOR LOAD INCREMENT" & Right("      " & LiveLoad, 6), After:=ActiveCell, LookIn _
            :=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _
            xlNext, MatchCase:=False).Activate
        shearbegin1 = Right(ActiveCell.Offset(6 + k, 0), 10)
    
        
        Range("A1").Activate
       
                 
    ActiveWorkbook.Close
    Application.ScreenUpdating = False
    
        Windows(MyMacroFile).Activate
        'span 1 values
        Range("J" & i) = posbegin1
        Range("K" & i) = negbegin1
        Range("L" & i) = compbegin1
        Range("M" & i) = shearbegin1
    
        'COPY VALUES TO SUMMARY TABLE
                
    Next i
    

    
End Sub

 

[jrisebo]

Here it the error message recieved when trying to process alot of files based on the code. I tried adding

Application.CutCopyMode = False

to the code but still getting the error.

 

[Logit]

Another possibility is your Excel workbook may contain non-viewable data in the rows below viewable data within each sheet. The following macro
will review each sheet in your workbook and remove this extraneous data ... in essence cleaning the junk and leaving only your presently viewable
data.

NOTE : Only perform this macro action on a COPY of your workbook ... not the original. If the macro is successful and doesn't alter your existing data
then you can decide to use it on your original workbook.

Sub LoseThatWeight()

    Dim x As Long, Lastrow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

    For x = 1 To Sheets.Count
        With Sheets(x)
            Lastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[jrisebo]

Another possibility is your Excel workbook may contain non-viewable data in the rows below viewable data within each sheet. The following macro
will review each sheet in your workbook and remove this extraneous data ... in essence cleaning the junk and leaving only your presently viewable
data.

NOTE : Only perform this macro action on a COPY of your workbook ... not the original. If the macro is successful and doesn't alter your existing data
then you can decide to use it on your original workbook.

Sub LoseThatWeight()

    Dim x As Long, Lastrow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

    For x = 1 To Sheets.Count
        With Sheets(x)
            Lastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Are you thinking the data is being placed each time the code cycles thru the files?

 

[Logit]

Are you thinking the data is being placed each time the code cycles thru the files?

That might be a possibility but ... seeing if the macro will clear out the extraneous, unviewable data to begin with might clear things up.
Check the size of the workbook BEFORE running the macro. Then review the workbook size afterwards. See if the size has been reduced.

 

[jrisebo]

That might be a possibility but ... seeing if the macro will clear out the extraneous, unviewable data to begin with might clear things up.
Check the size of the workbook BEFORE running the macro. Then review the workbook size afterwards. See if the size has been reduced.

Ok thanks.

 

[Bond00]

Ok thanks.

Just curious did that fix the issue?
I also do something similar to this but with a large amount of .xlsx files in a folder and not .txt

 

[jrisebo]

Just curious did that fix the issue?
I also do something similar to this but with a large amount of .xlsx files in a folder and not .txt

Thanks, sorry was out for an extended time. Will review and let you know if it works.