The .FileDialog() method takes a parameter in the form of fileDialogType. You can choose the type from a list of predefined values (or constants). These are,
1) msoFileDialogFilePicker: Opens a dialog box to select files.
2) msoFileDialogFolderPicker: Opens a dialog box to select folders.
3) msoFileDialogOpen: Allows users to open a file.
4) msoFileDialogSaveAs: Opens a dialog box as Save as that allows users to save a file.
Now let’s see how we can use it in Excel.
You will have first create an instance of the Application object with the .fileDialog() method. Therefore, open the VBA editor in Excel. I am using a button (an ActiveX Button control) to open the file dialog. So, add a button in your worksheet or sheet1.
Open a File Dialog Box in Excel
Here’s the code to open a file dialog box in excel. It also show other properties the object provides, such as
a) the .Filter property to show only specific file types in the folder
b) the .AllowMultiSelect property to either select a single file or multiple files.
Option Explicit Private Sub CommandButton1_Click() ' Create and set the file dialog object. Dim fd As Office.FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Filters.Clear ' Clear all the filters (if applied before). ' Give the dialog box a title, word for doc or Excel for excel files. .Title = "Select a Word File" ' Apply filter to show only a particular type of files. ' For example, *.doc? to show only word files or ' *.xlsx? to show only excel files. .Filters.Add "Word Files", "*.doc?", 1 ' Do not allow users to select more than one file. ' Set the value as "True" to select multiple files. .AllowMultiSelect = False ' Show the file. If .Show = True Then Debug.Print .SelectedItems(1) ' Get the complete file path. Debug.Print Dir(.SelectedItems(1)) ' Get the file name. End If End With End Sub
In the above example, I have set the value as False for .AllowMultiSelect property, which means you cannot select more than one file. However, if you set the value as True it will allow you to select multiple files and to read all selected file, you can simply run a loop like this,
.AllowMultiSelect = True If .Show = True Then Dim i For i = 1 To .SelectedItems.Count Debug.Print .SelectedItems(i) Next i End If
Once you have access to the file, you can open it, read its contents or even manipulate its contents. I have explained it here.
Similarly, you can select .xls or .xlsx files (all Excel format) using the .FileDialog() method and view its contents. For example,
Option Explicit Private Sub CommandButton1_Click() ' Create and set the file dialog object. Dim fd As Office.FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Filters.Clear .Title = "Select an Excel File" .Filters.Add "Excel Files", "*.xlsx?", 1 .AllowMultiSelect = False Dim sFile As String If .Show = True Then sFile = .SelectedItems(1) End If End With If sFile <> "" Then Workbooks. Open sFile ' Open the Excel file. End If End Sub
Open the File Dialog in a Specific Folder
You can tell the FileDialog method to open the dialog box in a specific folder. Usually, the file dialog opens in a default folder such as the Documents folder. However, if you know where your files are, then you can provide the folder name with its full path to the method. For example, if I want to open a folder in D drive, I’ll do this,
Dim fd As Office.FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Filters.Clear .Title = "Select an Excel File" .Filters.Add "Excel Files", "*.xlsx?", 1 .AllowMultiSelect = False .InitialFileName = "d:\movies" End With
Simply provide the path of the folder to the .IntialFileName property. That’s it.
Thanks for reading.