This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Selecting a file using the FileDialog

VBA Code Snippets

VBA Code Snippets

In a Macro or Add-in there may be times when you require the user to select a file.  It is possible to use the standard FileDialog box for this purpose.  The following code is intended to provide the most common settings which you can amend to meet your needs.


Advertisement:

 

Open the file DialogBox for selecting a file

Sub selectFile()

Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogSaveAs)

'Set the display properties - these are optional
'All the settings must be applied before the .Show command

'Do not allow multiple files to be selected
dialogBox.AllowMultiSelect = False

'Set the title of of the DialogBox
dialogBox.Title = "Select a file"

'Show the dialog box and output full file path and file name
If dialogBox.Show = -1 Then
   MsgBox "You selected: " & dialogBox.SelectedItems(1)
End If

End Sub

 

Selecting multiple files


Advertisement:

If you the user is allowed to select more than one file the following code can be used:

Sub selectFiles()

Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogSaveAs)
Dim i As Integer
'Set the display properties - these are optional
'All the settings must be applied before the .Show command

'Allow multiple files to be selected
dialogBox.AllowMultiSelect = True

'Set the title of of the DialogBox
dialogBox.Title = "Select a file"

'Show the dialog box
'Loop through each selected item and output to Immediate Window
For i = 1 To dialogBox.SelectedItems.Count
    Debug.Print dialogBox.SelectedItems(i)
Next i

End Sub

 

Setting the default folder options

'Set the default folder to open
dialogBox.InitialFileName = "C:\Users\marks\Documents"

'Set filters to show specific files types only - these are optional
'Clear any existing filters first
dialogBox.Filters.Clear
'Add more filters - use ; to separate filters for the same name
dialogBox.Filters.Add "Excel workboooks", "*.xlsx;*.xls;*.xlsm"

Advertisement:

 

File name or full file path

Sometimes you want the full file path of the selected item

'Show the full file path (folder & file name)
MgsBox dialogBox.SelectedItems(i)

Other times, you just want the name of the file (not the folder)

'Just show the file name
MsgBox Dir(dialogBox.SelectedItems(1))