In this article, we will create a macro to open multiple files with a file dialog box.
We have created the macro “opening_multiple_file” to open multiple files. A user can click on “Open Multiple files” button to run the macro.
Logic explanation
We have created the macro “opening_multiple_file” to open multiple files. A user can click on “Open Multiple files” button to run the macro. It opens the file dialog box from where a user can select multiple excel files, which he wants to open.
Once all the files are selected, click on “OK” button to open all the selected files.
Code explanation
Application.FileDialog(msoFileDialogFilePicker)
The above code is used to open the file dialog box.
.AllowMultiSelect = True
In above code, AllowMultipleSelect property is set true to enable multiple selection in file dialog box.
.Filters.Add "Excel Files", "*.xls*"
In above code, filter for file dialog box is set to select Excel Files only.
If .Show = True Then
The above code is used to check whether a user presses “OK” or “Cancel” button in file dialog box.
Workbooks.Open .SelectedItems(i)
The above code is used to open the selected Excel file.
Please follow below for the code
Option Explicit
Sub opening_multiple_file()
Dim i As Integer
'Opening File dialog box
With Application.FileDialog(msoFileDialogFilePicker)
'Enabling multiple files select
.AllowMultiSelect = True
.Filters.Clear
'Only Excel files can be selected
.Filters.Add "Excel Files", "*.xls*"
If .Show = True Then
For i = 1 To .SelectedItems.Count
'Opening selected file
Workbooks.Open .SelectedItems(i)
Next i
End If
End With
End Sub
If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I can open all files at once by using your macro above however I also need to add a MsgBox to ask for a password but I can't seem to get it to work. The password cannot be hard coded in the macro as it needs to be generic so that is is used by a team who all have different passwords for their files. When I add a msgbox into the code and run it I am being asked to key in the password for each file but I only want to type the password in once as all the files selected have the same password. Can you help please?
now i want to set name for each opened file serially such as wkb1, then opened file wkb2, then wkb3 with this code ?....whats the syntax for that?
i want to open several files in a folder and set a name for each workbook when its open ... how can i do it with this code????
whats the syntax for that?
Pls Help
How can this be applied to a zipped folder?