From a quick look at your code, it seems you have commented out the line
'For Each file In Folder.Files
Since it is this line of code that sets the 'file' variable to a specific
File object, you will get an "object not set" error because the variable
'file' hasn't been set to any File in particular. Somehow, you need to set
'file' to a File object. Perhaps
Set file = oFSO.GetFile("C:\Whatever\Test.xls")
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"Rick S." wrote in message
...
I have this code (Thank you Bob Phillips):
Code:
Sub LoopFolders()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("c:\MyTest")
For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing
End Sub
Modified as so:
Code:
Sub OpenWorkbooks()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
'Modified macro from Bob Phillips
'Application.ScreenUpdating = False 'temporarily disabled
ThisWorkbook.Worksheets("INSTRUCTIONS").Activate 'select cells from
"INSTRUCTIONS" workbook
Cells.Copy 'copy all cells
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("U:\VBA\Test Folder")
'For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then 'ERROR IS AT THIS LINE
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Worksheets("INSTRUCTIONS").Activate 'select worksheet
"INSTRUCTIONS"
ActiveSheet.Paste 'paste cells from original worksheet
ActiveWorkbook.Close SaveChanges:=True
End If
'Next file
Set oFSO = Nothing
End Sub
Neither one will work and I get the follwoing error:
"Object varaible or With block variable not set"
What is failing?