Welcome to Excel Avon
File Dialog Box & Folder Dialog Box in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
While working there may be some scenarios where we need to open some other workbooks or any other file to import data or take reference. In VBA there is a way to provide the path of a file in our code which will open the file or folder for us.
In FileDialog, there are four different types of constants: msofiledialogfilepicker, which one can use to select a file from a given path. The second one is msofiledialogfolderpicker which the name suggests, one can pick a folder, and the third is msofiledialog open to open a file. The last is msofiledialogsaveas which one can use to save a file as a new file.
Use File Dialog Box & Folder Dialog Box in Excel VBA
SYNTAX
Application.FileDialog(fileDialogType)
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: This allows the user to select a file.
2. msoFileDialogFolderPicker: This allows the user to select a folder.
3. msoFileDialogOpen: This allows the user to open a file.
4. msoFileDialogSaveAs: This allows the user to save a file.
We have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine for File DialogBox.
Sub UseDialogBox() End Sub
Here we will define variables for File Dialog and Lr as long variable where Lr is long range, i as long.
Sub UseDialogBox() Dim FD As FileDialog Dim lr As Long, i As Long End Sub
Now we will set up the file dialog, For File Dialog we will select msoFileDialogFilePicker.
Sub UseDialogBox() Dim FD As FileDialog Dim lr As Long, i As Long Set FD = Application.FileDialog(msoFileDialogFilePicker) End Sub
Now we need to make the dialog box for this example, let’s us use with statement as Follow. Inside the with statement, we must put a dot to see the IntelliSense list of the properties and methods of the FileDialog option. Now do we want the user to select multiple files at once or a single file? For this example, let us stick with the single file at a time, so use .Allowmultiselect as false.
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
End with
End Sub
Now I will add the title and give a new name to the title, with this we will give the name ‘choose’ to the add button.
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.Title = "Choose Excel File"
.ButtonName = "Choose"
End with
End Sub
Now we will clear all the filters then add some new filters xlsx, xls.
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx, *.xls"
End with
End Sub
Now in order to show the dialog box we can write .show to show the dialog box as follows and create a condition, when file has been selected,
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx, *.xls"
If .Show = True Then
If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
End with
End Sub
But first we have to define the value of lr, we have one more condition for last row Lr =Lr+1
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
lr = 1
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx, *.xls"
If .Show = True Then
If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
lr = lr + 1
End with
End Sub
Here we will add range to activatesheet
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
lr = 1
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = False
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx, *.xls"
If .Show = True Then
If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
lr = lr + 1
ActiveSheet.Range("A" & lr).Value = .SelectedItems(i)
End with
End Sub
Now we will click on run button
After clicking run button, a pop-up window opened that title named ‘choose Excel File’ will open. See all the filter that we have added here. Adding a filter will show only xlsx and xls files here, Button name changed. ‘Multiple files select’ is set to false, so we will not be able to select multiple files.
We selected a file which can be seen here
Going to insert button will assign search button, select drag macro and rename button. Here we will drag the button, assign the macro, then name the button.
Clicking on the choose button will open a pop up where we can select the file.
After choosing the file will appear here
Let’s make the multiple select true and see how it will work
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
lr = 1
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = True
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx, *.xls"
If .Show = True Then
If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
lr = lr + 1
ActiveSheet.Range("A" & lr).Value = .SelectedItems(i)
End with
End Sub
Go back to worksheet and click the assign button, Hold the ctrl button and select multiple files now
We had selected multiple files by holding the ctrl button, due to which the selected files have been picked up in the excel worksheet.
Now we will go into VBE and add new filters, here we have ‘xls?’, ‘xl?’ type file filter added.
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
lr = 1
Set FD = Application.FileDialog(msoFileDialogFilePicker)
With FD
.AllowMultiSelect = True
.Title = "Choose Excel File"
.ButtonName = "Choose"
.Filters.Clear
.Filters.Add "Excel Files", "*.xls?, *.xl?"
If .Show = True Then
If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
lr = lr + 1
ActiveSheet.Range("A" & lr).Value = .SelectedItems(i)
End with
End Sub
After adding the filter, we will go to the worksheet and click on the Choose button to select the file. Adding filter will be beneficial that files with xlsm will also be filtered as well as xlsx.
Multiple selection is true so we can pick multiplefiles, hold ctrl key and select multiple files.
You can see multiple files are picked
Folder Dialog box in Excel VBA
Go back to VBE, Use the file dialog Box for Folderpicker and Multiple selection doesn’t work in folder picker so we will delete it, Filter will also not work in folder picker, so we will delete it too. Similarly, we will give the title choose excel folder. After so many changes we will go to the worksheet.
Sub UseDialogBox()
Dim FD As FileDialog
Dim lr As Long, i As Long
lr = 1
Set FD = Application.FileDialog(msoFileDialogFolderPicker)
With FD
.Title = "Choose Excel Folder"
.ButtonName = "Choose"
If .Show = True Then If.SelectedItems.Count>0 Then
For i = 1 To .SelectedItems.Count
lr = lr + 1
ActiveSheet.Range("A" & lr).Value = .SelectedItems(i)
End with
End Sub
After coming to the sheet, we will click on the assign button and choose the Folder. If you see, you will find the filters removed from here. We will be able to select only folders here, cannot select multiple folders.
As you can see, we have selected the program folder.
So, I hope you have understood Use File Dialog box & folder Dialog box in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about Use File Dialog box & folder Dialog box in Excel VBA
