Open multiple files at once

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.

ArrowMain

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.

ArrowSelectingMultipleFiles

Once all the files are selected, click on “OK” button to open all the selected files.

ArrowOutput

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

Users are saying about us...

  1. 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

  2. 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?

  3. Kathleen McGugan

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube