VBA Run-time Error Opening Files in a Folder-CodePudding

I am trying to open all files in a specified folder. This is the code I have (I have put Path and FolderName as generic but they work and the code gets the correct path and correct folder name when I run it):

'''

Dim FolderName As String, FSOLibrary As Object, FSOFolder As Object, FSOFile As Object

FolderName = Path
'set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(FolderName)

Set FSOFile = FSOFolder.Files

For Each FSOFile In FSOFile
currentfile = FSOFile.Name
Workbooks.Open FSOFile.currentfile

'''

When I execute the code I get run time error, object doesn't support this property or method. However the currentfile always has the correct filepath and name. Unsure why it is unable to open the files.

CodePudding user response：

Two things I've noticed, I believe when using For Each in VBA you need to first create a variable of type Variant. Also, you've written For Each FSOFile in FSOFile which would be invalid anyway because you're trying to loop a variable through itself...

Try changing the bottom part of your code to this:

Set FSOFiles = FSOFolder.Files

Dim FSOFile as Variant

For Each FSOFile in FSOFiles
currentfile = FSOFile.Name
Workbooks.Open FSOFile.currentfile

CodePudding user response：

You have to pass to Workbooks.Open a full file name, in your case I assume it is

Workbooks.Open Path & "\" & currentfile

An improved version of your code would look like

Dim FolderName As String, FSOLibrary As Object, FSOFolder As Object, FSOFile As Object, FSOFiles As Object
    
FolderName = Path
'set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(FolderName)
    
Set FSOFiles = FSOFolder.Files

For Each FSOFile In FSOFiles
    Workbooks.Open FSOFile.Path & "\" & FSOFile.Name
    'Debug.Print FSOFile.Path & "\" & FSOFile.Name
Next