auto.pilot
Well-known Member
- Joined
- Sep 27, 2007
- Messages
- 734
- Office Version
- 365
- Platform
- Windows
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
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
Code:
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
Last edited: