To open all Excel files in a folder

[murali9193]

Hi All,

i need a simple VBA code
i want to open all Excel files in a folder if file format it may be difference like.xlsx,xlsm

Thanks in advance.
Murali

 

[Luke M]

Hi Murali,

Seems like a strange request, but here you go...

Sub OpenAllFiles()
Dim fPath As String
Dim fName As String

With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select a folder"
    .Show
    If .SelectedItems.Count = 0 Then Exit Sub
    fPath = .SelectedItems(1)
End With

'Error check
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

fName = Dir(fPath & "*.xls*")

Application.ScreenUpdating = False
Application.StatusBar = "Opening files..."
Do While fName <> ""
    Workbooks.Open fPath & fName
    fName = Dir()
Loop

Application.StatusBar = False
Application.ScreenUpdating = True
End Sub