VBA/Excel/Access/Word/File Path/FileDialog

Displays a dialog box that allows the user to select a directory. The selected directory name (or "Canceled") is then displayed by using the MsgBox function.

 
  Sub GetAFolder()
      With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a location for the backup"
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Canceled"
        Else
            MsgBox .SelectedItems(1)
        End If
      End With
  End Sub

FileDialogFilters

 
     Private Sub cmdGetFile_Click()
         Dim fd As FileDialog
         Dim ffs As FileDialogFilters
         Dim vItem
         On Error GoTo Problem
         Set fd = Application.FileDialog(msoFileDialogOpen)
         With fd
             Set ffs = .Filters
             With ffs
               .clear
               .add "Pictures", "*.jpg"
             End With
             .AllowMultiSelect = True
             If .show = False Then Exit Sub
             For Each vItem In .SelectedItems
               Debug.Print vItem
             Next vItem
         End With
         Exit Sub
Problem:
         MsgBox "That was not a valid picture"
     End Sub

FileDialog with JPG file filter

 
     Private Sub cmdGetFile_Click()
         Dim fd As FileDialog
         Dim ffs As FileDialogFilters
         On Error GoTo Problem
         Set fd = Application.FileDialog(msoFileDialogOpen)
         With fd
             Set ffs = .Filters
             With ffs
               .clear
               .add "Pictures", "*.jpg"
             End With
             .AllowMultiSelect = False
             If .show = False Then Exit Sub
             Image1.Picture = LoadPicture(.SelectedItems(1))
         End With
         Exit Sub
Problem:
         MsgBox "That was not a valid picture"
     End Sub

Get selected paths

 
Public Sub ShowFileDialog()
    Dim fd As FileDialog
    Dim selectedPaths() As String
    Dim I As Integer
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd     "Configure dialog box
        .AllowMultiSelect = True
        .FilterIndex = 2
        .Title = "Select Excel File(s)"
        .InitialFileName = ""
        "Show the dialog and collect file paths selected by the user
        If .Show = -1 Then   "User clicked Open
            ReDim selectedPaths(.SelectedItems.Count - 1)
            "Store file paths for later use.
            For I = 0 To .SelectedItems.Count - 1
                selectedPaths(I) = .SelectedItems(I + 1)
            Next I
        End If
        .Execute     "Open selected files
    End With
    Set fd = Nothing
End Sub

Open File Open Dialog and get the selection (Dialog Types Used with the FileDialog Object)

 
Dialog Type      VBA Constant (FileDialogType)         
Open             msoFileDialogOpen         
Save             msoFileDialogSaveAs         
File Picker      msoFileDialogFilePicker         
Folder Picker    msoFileDialogFolderPicker       
Sub openDlg()
    Dim fc As FileDialogFilters
    Dim ff As FileDialogFilter
    Set fc = Application.FileDialog(msoFileDialogOpen).Filters
    Set ff = fc.Item(1)
    MsgBox ff.Description & ff.Extensions     "Displays "AllFiles" and *.*
End Sub

Set the AllowMultiSelect property of the dialog box to allow multiple selections in the dialog box

 
Sub OpenDialog()
    Dim dlgOpen As FileDialog
    Set dlgOpen = Application.FileDialog( _
        DialogType:=msoFileDialogOpen)
    With dlgOpen
         .AllowMultiSelect = True
         .Show
    End With
    msgBox dlgOpen.SelectedItems(1)
End Sub

The FileDialog Object

 
Sub SaveDialog()
    Dim dlgSaveAs As FileDialog
    Set dlgSaveAs = Application.FileDialog(DialogType:=msoFileDialogSaveAs)
    dlgSaveAs.Show
    msgBox dlgSaveAs.SelectedItems(1)
End Sub

User selects path to save HTML files

 
Private Sub cmdChangePath_Click()
    Dim fd As FileDialog
    Dim I As Integer
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .AllowMultiSelect = False "Allow only one selection
        .Title = "Select Folder"
        .InitialFileName = ""
        If .Show = -1 Then
            MsgBox .SelectedItems(1)
        End If
    End With
    Set fd = Nothing
End Sub

Use the SelectedItems property of the FileDialog object to return the FileDialogSelectedItems collection.

 
Sub selected()
    Dim si As FileDialogSelectedItems
    Set si = Application.FileDialog(msoFileDialogOpen).SelectedItems
End Sub

You can use the Add method of the FileDialogFilters collection object to create your own list of filter

 
Sub fileDlg()
    Dim fd As FileDialog
    Dim imagePath As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "All files", "*.*"
        .Filters.Add "Image", "*.jpg", 1
        .FilterIndex = 1
        .InitialFileName = ""
        .Title = "Select JPEG file"
        If .Show = -1 Then       "User pressed action button
            imagePath = .SelectedItems(1)
        End If
    End With
End Sub