Seeking assistance with run time error 432

[auto.pilot]

I have the following code, which simply opens workbooks that are all in the same folder (this is the source). the code then copies a range on the General worksheet within each of the workbooks. The range is then pasted to the destination workbook (the one with this code). All of the workbooks in the source folder are macro enabled as is the destination workbook.

There are 66 workbooks in the source folder. This code works perfectly for the first 58 source workbooks, but hangs on the next one. I can't see any reason for the error, which is "File name or class name not found during Automation operation". I verified that each workbook has a sheet named General.

how can I fix this?

Thanks in advance.

Jim

Option Explicit
Const SOURCE_FOLDER = "\\server\Files\Clients\Portfolios\"


Sub AggregateDataFromFiles()
          
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    Dim fs As Object
    Dim objFolder As Object
    Dim objFolderName As String
    objFolderName = SOURCE_FOLDER
    Dim filePath As String
    Dim objFile As Object
    Dim targetWb As Workbook
    Dim lastrow As Long
    Dim TopRow As Long
    Dim BottomRow As Long
    Cells.ClearContents
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = fs.GetFolder(objFolderName)
    For Each objFile In objFolder.Files
        
        filePath = objFolderName & "\" & objFile.Name
        Set targetWb = GetObject(filePath)
        
        targetWb.Worksheets("General").AutoFilterMode = False
        
        targetWb.Worksheets("General").Range("A3:AX1000").Copy
        
        lastrow = Range("C" & Rows.Count).End(xlUp).Row
        ActiveSheet.Range("B" & lastrow + 1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

        targetWb.Close (False)
  
    Next
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[cerfani]

when you debug, is the value passed into the open function a valid file name and the file is closed? Maybe it is a problem with memory but you are closing the workbooks so that should free memory each loop.

Try running the macro with only that 57th book in the folder so it processes it first and see what happens.

 

[auto.pilot]

when you debug, is the value passed into the open function a valid file name and the file is closed? Maybe it is a problem with memory but you are closing the workbooks so that should free memory each loop.

Try running the macro with only that 57th book in the folder so it processes it first and see what happens.

Thanks again for all your assistance. The issue appears to be related to memory. I created two new 'Source' folders, then put 33 workbooks in each one. Thereafter, I was able to run the code separately for both folders and everything worked.

Thanks

jim