Using VBA Code (Excel 2007) To Open All Excel Files in a specific folder

[mwmtech]

Hi Guys, this code is causing me some difficulty. I am trying to use the code below in Excel 2007 to open all Excel files in a specified folder, but I am getting the run-time error '1004', any ideas on what I am doing wrong? Thanks in advance..

Sub FindOpenFiles()
Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file, wb As Workbook
Dim directory As String
directory = "C:\Users\Documents\VBA\VBA 2\Data"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(directory)
Set wb = Workbooks.Open(directory)
For Each file In folder.Files
Workbooks.Open wb
Next file
End Sub

 

[Norie]

You don't actually appear to be opening the files that are specified with 'folder.Files'.

This appears to be trying to open a file with the name of the directory you want to look in.

Set wb = Workbooks.Open(directory)

I would recommend you just remove that line and within the loop try using file.

Sub FindOpenFiles()
Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file, wb As Workbook
Dim directory As String
 
    directory = "C:\Users\Documents\VBA\VBA 2\Data"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folder = FSO.GetFolder(directory)
    
    For Each file In folder.Files
        Workbooks.Open file
    Next file
End Sub

 

[mwmtech]

Thanks alot Norie, works now...

 

[cmefly]

what if i'm just asking to open xls files?????