Automation Error. Exception occured (possibly a memory issue?)

[wist]

Hi Experts,

I wrote below code in a form for my team. It's a simple 'open file - copy - paste' exercise but for some reason a bunch of users are getting these random Automation Errors. I believe it happens when two big files are opened.



Some users fix this after a restart and I had one user to reinstall his Excel to 64-bit and it also fixed the issues, so I suspect the memory gets overloaded by the macro?

I'm wondering what part of the code might be so heavy that this happens for some and not for others.

Thanks for your inputs

Sub MatchRecs()Dim i, x As Long
Dim Rec, Partnerec As Workbook
Dim CoCd, lastrow As String


'open my MainRec
'loop through open workbooks to see if the rec is already opened, if yes close it
For Each Workbook In Workbooks
    If Workbook.Name = "Intercompany Recs " & Country.Value & " # " & FileBoxItem Then
        Workbooks("Intercompany Recs " & Country.Value & " # " & FileBoxItem).Close True
        Exit For
    End If
Next Workbook
            
    Set Rec = Workbooks.Open(filename:=path & FYBox.Value & "\" & PeriodBox.Value & "\Intercompany Recs " & Country.Value & " # " & FileBoxItem, UpdateLinks:=False)


    CoCd = Rec.Sheets("Data").Range("E2").Value
    
'loop through all partners
    For i = 1 To Rec.Sheets("One Look").PivotTables("OneLook").PivotFields("Tr.Prt").PivotItems.Count


'lookup partner country folder name
        Rec.Sheets("One Look").Cells(i + 9, 50).Value = "=IFNA(VLOOKUP(""" & Rec.Sheets("One Look").Cells(i + 9, 1).Value & """, '" & Left(MasterPath, Len(MasterPath) - 15) & "[MasterFile.xlsx]Master'!$A:$H, 8, 0),0)"


'check if there is already data in the pivot and if partner rec exists and it's not the same file as Rec
        If Rec.Sheets("One Look").Range("J" & i + 9) & Rec.Sheets("One Look").Range("K" & i + 9) & Rec.Sheets("One Look").Range("L" & i + 9) & Rec.Sheets("One Look").Range("M" & i + 9) & Rec.Sheets("One Look").Range("N" & i + 9) & Rec.Sheets("One Look").Range("O" & i + 9) = "" _
            And Dir(IntercoFolder & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " interco\7 RECONCIL\" & FYBox.Value & "\" & PeriodBox.Value & "\Intercompany Recs " & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " # " & Rec.Sheets("One Look").Cells(i + 9, 1).Value & ".xlsb") <> "" _
            And Dir(IntercoFolder & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " interco\7 RECONCIL\" & FYBox.Value & "\" & PeriodBox.Value & "\Intercompany Recs " & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " # " & Rec.Sheets("One Look").Cells(i + 9, 1).Value & ".xlsb") <> Rec.Name Then
           
'open partner rec
           Set Partnerec = Workbooks.Open(filename:=IntercoFolder & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " interco\7 RECONCIL\" & FYBox.Value & "\" & PeriodBox.Value & "\Intercompany Recs " & Rec.Sheets("One Look").Cells(i + 9, 50).Value & " # " & Rec.Sheets("One Look").Cells(i + 9, 1).Value & ".xlsb", UpdateLinks:=False, ReadOnly:=True)
           
            Application.DisplayAlerts = False
            Application.Calculation = xlCalculationManual
            
'if partner rec is already matched, remove the data to avoid duplication
                With Partnerec.Sheets("Data").ListObjects("Data")
                    .Range.AutoFilter field:=1, Criteria1:="Partner AP", Operator:=xlOr, Criteria2:="Partner AR"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
                    .Range.AutoFilter field:=1, Criteria1:="Partner AP LOAN", Operator:=xlOr, Criteria2:="Partner AR LOAN"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
                    .AutoFilter.ShowAllData
                    
'amend AP/AR to fit partner match format and copy relevant columns
                    .Range.AutoFilter field:=1, Criteria1:="AP"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Value = "Partner AP"
                    .Range.AutoFilter field:=1, Criteria1:="AR"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Value = "Partner AR"
                    .Range.AutoFilter field:=1, Criteria1:="AP LOAN"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Value = "Partner AP LOAN"
                    .Range.AutoFilter field:=1, Criteria1:="AR LOAN"
                 If .ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Value = "Partner AR LOAN"
                    .AutoFilter.ShowAllData
                    .ListColumns(5).Range.Cut
                    .ListColumns(3).Range.Insert Shift:=xlToRight
                    .ListColumns(4).Range.Cut
                    .ListColumns(6).Range.Insert Shift:=xlToRight
                    .Range.AutoFilter field:=5, Criteria1:=CoCd
                 If .ListColumns(5).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then
              Union(.ListColumns(3).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(4).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(5).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(6).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(7).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(8).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(9).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(10).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(11).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(12).DataBodyRange.SpecialCells(xlCellTypeVisible), _
                    .ListColumns(13).DataBodyRange.SpecialCells(xlCellTypeVisible)).Copy
                    
'paste data to mainrec and close partner without saving
       lastrow = Rec.Sheets("Data").Range("E1").End(xlDown).Row + 1
                 Rec.Sheets("Data").Range("C" & lastrow).PasteSpecial xlPasteValues
                    .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
                 Rec.Sheets("Data").Range("A" & lastrow).PasteSpecial xlPasteValues
                  End If
                End With
                              
            Partnerec.Close False
        End If
    Next i
    
'remove partner folder names and save
        Rec.Sheets("One Look").Columns(50).ClearContents
        Application.Calculation = xlCalculationAutomatic
        Rec.RefreshAll
        Application.DisplayAlerts = True
    
    Rec.Close True
    
End Sub

 

[Norie]

Does the code stop at a particular point?

Have you checked in Task Manager what's happening with the memory Excel is using?

 

[bobsan42]

Clean up/fix your code according to my remarks below, then if the problem persists include detailed error information.
- Always use Option explicit statement at the top of your code modules - helps avoiding a lot of mistakes and declaring variables more helpful than it is tedious.
- Try to be explicit when you refer to controls, ranges, etc. - Since the code is in a form I assume that FYBox, PeriodBox, Path, MasterPath, IntercoFolder etc.are control names but it is better to refer to them as Me.FYBox, Me.PeriodBox, .... if the code is in the form code module
- Debug / Compile your code before running it and deal with any errors encountered.
- Avoid using variables with names like Workbook, Sheet, Range etc. - these are reserved words and best be avoided as variable names
- It is also very good practice to clear all unnecessary variables before exiting the procedure.

 

[wist]

Does the code stop at a particular point?

Have you checked in Task Manager what's happening with the memory Excel is using?

Yes, I had two users check this and both had around 65% memory usage and one was getting the error and one didn't

I read that memory cap for 32bit excel is 2gb but after it uses more than 700mb it can start to behave abnormally :D that's not even 50% capacity

 

[wist]

Clean up/fix your code according to my remarks below, then if the problem persists include detailed error information.
- Always use Option explicit statement at the top of your code modules - helps avoiding a lot of mistakes and declaring variables more helpful than it is tedious.
- Try to be explicit when you refer to controls, ranges, etc. - Since the code is in a form I assume that FYBox, PeriodBox, Path, MasterPath, IntercoFolder etc.are control names but it is better to refer to them as Me.FYBox, Me.PeriodBox, .... if the code is in the form code module
- Debug / Compile your code before running it and deal with any errors encountered.
- Avoid using variables with names like Workbook, Sheet, Range etc. - these are reserved words and best be avoided as variable names
- It is also very good practice to clear all unnecessary variables before exiting the procedure.

I will go through the code and give it a shot. I never paid attention to Option Explicit but now that I read up on it, it seems to me like a must have. I'll let you know if there's any progress.

I thought that when the code is within the form referring to Me. seems redundant. Would you have an explanation why should I add it anyway?

Thanks

 

[bobsan42]

This tells the code what exactly you refer to - otherwise it has to guess and there are cases when it may guess wrong.
Imagine your address - you tell me the which Country and the street name but you don't tell me which city - I may guess right (depending on the uniqueness of the street name) but I may also guess wrong, so if i'm the postman you may never get delivered the letter from your lover (sorry, just couldn't hold myself).
Plus I think it is much easier to code once you get used to it.

But the first thing to do - rename the Workbook variable to Wbk or something.

 

[Norie]

wist

When running the code was there a noticeable increase in the memory being used as each pivot table/workbook was being dealt with?

 

[wist]

wist

When running the code was there a noticeable increase in the memory being used as each pivot table/workbook was being dealt with?

no, it was oscilating around +- 15%

 

[Norie]

If you remove/comment this out do you get any warnings?

Application.DisplayAlerts = False