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]

Try Workbooks.Open(filePath) instead of CreateObject... i assume that is the line that is crashing... you didn't mention

Set targetWb = Workbooks.Open(filePath)

 

[auto.pilot]

Thank you for your reply. However, changing only that one line results in the copied range being pasted into the source workbook, not the destination workbook. Any other thoughts would be appreciated.

Thanks

jim

 

[cerfani]

Because you are referencing the Active sheet (which is what you just copied from) instead of the destination workbook to paste. Either activate your sheet to paste to or reference the sheet directly.

 

[auto.pilot]

Because you are referencing the Active sheet (which is what you just copied from) instead of the destination workbook to paste. Either activate your sheet to paste to or reference the sheet directly.

I very much appreciate your help, but I am stumped. I've tried a number of methods, but can not figure out how to activate the destination workbook/worksheet after copy. Would appreciate any assistance.


Thanks

Jim

 

[cerfani]

if the code is being called from the workbook you are trying to paste to the, ThisWorkbook should reference the workbook that called the code. Then from that you can reference the sheet. Do you know the sheet name you are pasting to??

If yes, then you can use this...

ThisWorkbook.Worksheets("Sheet Name") 'fix the sheet name

Or if you know the name of the workbook or the code is being called from another workbook like the personal workbook then you can try to use the workbook file name...

Workbooks("workbook name.xlsx").Worksheets("Sheet Name") 'fix the sheet name and workbook name

Calling Worksheet.Activate should make the sheet active but I would avoid coding in that style as it is harder to read. That is the kind of code the macro recorder creates. ActiveSheet is updated by Excel depending what you do so it can change unexpectedly, you should avoid referencing it in your code and create explicit references to objects.

 

[JonXL]

I very much appreciate your help, but I am stumped. I've tried a number of methods, but can not figure out how to activate the destination workbook/worksheet after copy. Would appreciate any assistance.


Thanks

Jim

You haven't yet clarified which line of the code is crashing.

 

[auto.pilot]

You haven't yet clarified which line of the code is crashing.

Thank you both for your replies. No matter what, the procedure fails with a specific workbook in the source folder. Odd, but it appears related to closing that file. I renamed it with a 1 in front of it, forcing that file to open first. The error message happens after just that file. It is running again now, with the offensive workbook forced to open last, with zzz in front of the file name. It appears that the code is running fine and that the issue is somehow related to just that file.

I appreciate your help, but the problem appears to be solved.

Thanks

Jim

 

[auto.pilot]

Thank you both for your replies. No matter what, the procedure fails with a specific workbook in the source folder. Odd, but it appears related to closing that file. I renamed it with a 1 in front of it, forcing that file to open first. The error message happens after just that file. It is running again now, with the offensive workbook forced to open last, with zzz in front of the file name. It appears that the code is running fine and that the issue is somehow related to just that file.

I appreciate your help, but the problem appears to be solved.

Thanks

Jim

I wrote that last message while the code was running, assuming that it would work. It did not.

This line is the problem. The code fails when opening the 57th workbook.

 Set targetWb = Workbooks.Open(filePath)

 

[auto.pilot]

Here's a thought... Often, I get 'out of memory' errors after opening and closing several workbooks without closing Excel. Within the destination workbook, VBA Project window, every source workbook that the code opens is listed. If I was simply opening these files without using this code, I would eventually get the Out of Memory Error. I suspect this is the issue, but don't know. Is there any way to clear out those references to closed files in the VBA Project window?