Added in Office 2002.
This provides a single object that allows you to display four different types of dialog box.
This includes dialog boxes for opening Files, saving Files, selecting Files and selecting Folders.
This is a much more powerful version of the previous GetOpenFileName and GetSaveAsFileName
One advantage of this FileDialog object is that you can display a list of just directories, rather than both directories and files.
This also has the advantage of being available in all the Office applications.

Set objFileDialog = Application.FileDialog(msoFileDialogType.msoFileDialogOpen) 

Types of Dialog Box

There is very little difference between the four types except the caption displayed at the top of the dialog box.
The FilePicker and FolderPicker display the word "Browse" in the caption while the other two display "File Open" and "File Save As".
They all display directories and files except the FolderPicker.

Set objFileDialog = Application.FileDialog(msoFileDialogType.msoFileDialogOpen) 

The Office prefix is not required but it makes it clear that it is an object in the Office library.

Public Sub File_Dialog_Test 
Dim objFileDialog As Office.FileDialog
Dim objFileDialogFilters As Office.FileDialogFilters
Dim sFileName As String

   Set objFileDialog = Application.FileDialog(msoFileDialogType.msoFileDialogOpen)
   With objFileDialog

'define a Filters object
      Set objFileDialogFilters = .Filters
      With objFileDialogFilters

'clear the default filters
'add a filter, all filters must start with an asterisk
         .Add "Client Files", "*.xls"
         .Add "Images", "*.png,*.jpg", 1
         .Add "All Files", "*.*"
      End With

'allow only one file to be selected
      .AllowMultiSelect = False

'show the dialog and exit if Cancel is pressed
      If .Show = False Then
         Exit Sub
      End If

'display the file name that was selected
      Call Msgbox("File selected : " & .SelectedItems(1))
   End With
End Sub

Displaying the Dialog Box

The Show method will display the dialog box.
This method will not actually open the files but just adds the selection to the SelectedItems collection.
If the user clicks Open then "True" is returned.
If the user clicks Cancel then "False" is returned.
You can use the Execute method to actually open or save the files.

If .Show = True Then 
End If

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext