VBA to open ONLY excel files in a folder

[Tanyaann1995]

Hi,

I want to open only Excel files in a folder and the below is the code which I'm using currently. But, I get an error message: "Compile Error: User defined type not defined" and the "Dim FSO" line gets highlighted.

Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file
Set FSO = CreateObject("Scripting.FileSystemObject")
a = wb.path
Set folder = FSO.GetFolder(a)
For Each file In folder.Files
        If Right(file, 5) = ".xlsx" Then
        Workbooks.Open file
        Else
        MsgBox ("No Datasheets found in Sizing folder")
        End If
    Next file

 

[Sektor]

You need to do one of the following:
1) Either use late binding:

Dim FSO As Object, folder As Object, file As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

2) Or use early binding. For it you need to: a) Create reference to Scripting library: Tools -> References -> Microsoft Scripting Runtime and b) change code this way:

Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file
Set FSO = New Scripting.FileSystemObject

 

[Tanyaann1995]

You need to do one of the following:
1) Either use late binding:

Dim FSO As Object, folder As Object, file As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

2) Or use early binding. For it you need to: a) Create reference to Scripting library: Tools -> References -> Microsoft Scripting Runtime and b) change code this way:

Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file
Set FSO = New Scripting.FileSystemObject

Thanks, this worked perfectly.

 

[Sektor]

Glad it helped! ? Haven't been here for a while! ?