Excel File Dialogs

There is a long list of built-in dialog boxes available in Excel. We can access and open those dialogs, and Windows dialogs too, with VBA code. In this post, we see how to launch and use file dialogs in Excel using VBA macros. We will see how to select a folder or file, and open or save a file (usually an Excel workbook). We also compare and discuss advantages and disadvantages of the different methods.

Types of Dialogs

There are more than two hundred dialogs available in Excel. We can launch a dialog with Excel VBA targeting the Dialogs object. When it comes to file dialogs, we could trigger the dialog to open a file simply as follows.

  

  Dim objDialog As Dialog
  Set objDialog = Application.Dialogs(xlDialogOpen)
  objDialog.Show

However, Excel has better ways to work with file dialogs. The most commonly used methods to open a file dialog with Excel VBA are using the property FileDialog or the function GetOpenFilename. The file dialog may look and behave slightly different depending on the method used and selected parameters. Here’s how the conventional Office dialog box to open a file looks like.

The file dialog prompts the user to select a folder or file, or to directly open or save the selected file or files. As we will see later, FileDialog and GetOpenFilename can set and modify some of the properties of the file dialog. We will see later in detail how each of them works, but for now let’s see a table with key characteristics for each of them.

FileDialog	GetOpenFilename
Property of Application object	Method of Application object
Allows to pick file, folder, open or save file (four dialog types)	Used only to open file (also can save)
Allows to filter by file type, whether to select only one or multiple files, to set the dialog box title, pre-setting the filename selected, changing how files are shown and the view of the button	Allows to filter by file type, whether to select only one or multiple files, and to set the dialog box title
Requires method “Show” to open dialog	Opens dialog directly
Can open/save the file with method “Execute” when dialog type is file open or save	Does not open file directly
May run a bit slower as it has more control	May run faster

  

We can also trigger Windows dialogs from within Excel using the shell application. We will see how to do that later in this article. For example, the Windows folder browser dialog looks as shown below.

 

FileDialog Property

The FileDialog property of the Application object returns an object representing an instance of a file dialog, which provides a dialog box functionality similar to those used to open and save a file in Excel and other Office applications. FileDialog accepts an argument (MsoFileDialogType) to determine the type of dialog, which can be one of the four possible options below.

• msoFileDialogFilePicker – File picker dialog box
• msoFileDialogFolderPicker – Folder picker dialog box
• msoFileDialogOpen – File open dialog box
• msoFileDialogSaveAs – File save as dialog box

 

The code below prompts a dialog box to select a file using the FileDialog property of the Application and the Show method of the generated FileDialog object. We could set certain properties such as title, file filter, etc. of the file dialog before showing the dialog (for example with myDialog.Title = "Select file to open").

 

  Dim myDialog As FileDialog
  Set myDialog = Application.FileDialog(msoFileDialogOpen)
  myDialog.Show

 

The conditional statement below checks if the file has been selected, and displays the path and name of the first selected file in a message box. The same would apply if we use any of the other dialog types (file or folder picker, or file save).

  

  If myDialog.Show = -1 Then
      MyDialogSelection = myDialog.SelectedItems(1)    
      MsgBox "The path of the selected file is " & MyDialogSelection
  End If

 

When using the file open (as in this example) or the file save dialog types, the method Execute can actually open or save the file or files, if we wish to do so. Otherwise, the file or files will not be open or saved, despite clicking on “Open” or “Save” in the dialog.

  myDialog.Execute

 

Another way to open the file or files, which also applies to the other dialog type (file picker), is using the Open method of the Workbook object along the file name and path retrieved. If this is new to you, please read more about it in this other page: Workbook object.

  Workbooks.Open MyDialogSelection

 

And another way to save the file is using the SaveAs method of the Workbook object as shown below to save the active workbook. We can save any other open workbook replacing “ActiveWorkbook” with “Workbooks(“nameofoworkbook.xlsx”). Read more about that in the other page (Workbook object) or download Excel VBA Objects Guide for Intermediate.

  ActiveWorkbook.SaveAs MyDialogSelection

 

When selecting more than one file, we can get access to each of them looping through the selected items collection as indicated below. This only works with msoFileDialogFilePicker or msoFileDialogOpen.

 

  For Each fileSelected In myDialog.SelectedItems
      MsgBox "The path of the selected file is " & fileSelected
  Next fileSelected

 

And if you are interested to see how to loop through the files when selecting a folder (msoFileDialogFolderPicker) have a look at this other article: List All Files in Folder

 

GetOpenFilename

The GetOpenFilename method of the Application object displays a standard file open dialog box to select a file or files, and gets the file name without actually opening the file. It shows a similar dialog to the one shown earlier for FileDialog with msoFileDialogOpen.

GetOpenFilename allows setting file filters and customizing the dialog title alongside (also possible with FileDialog after the object is created). The code below prompts an open dialog box with title “Select Files” to select only Excel files. MultiSelect allows to select multiple files when set to True.

 

  Dim selFiles As Variant
  selFiles = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls", _
  Title:="Select Files", MultiSelect:=True)

 

 

We can get the path and name of each file looping through the selected files (selFiles in the code above), in the same way we have seen earlier with the SelectedItems collection of the FileDialog property.

 

  Dim uFile As Variant
  For Each uFile In selFiles
      MsgBox uFile
  Next uFile

 

GetOpenFilename is a method and cannot take any other function to open the file as we have seen before with Execute for the FileDialog property/object. If we actually want to open the selected file or files, we need to use the Open method of the workbook (Workbooks.Open uFile).

We can also get the file name from a SaveAs dialog (as for FileDialog with msoFileDialogSaveAs) using the method GetSaveAsFilename, which displays the standard SaveAs dialog box and gets a file name without actually saving any files.

 

Windows Folder Dialog

We can also select and get the path to a folder using a Windows dialog. This would be similar to using FileDialog along msoFileDialogFolderPicker. However, the dialog looks quite different, the former was a conventional Office file dialog while this one is a Windows folder browser – see below.

To launch the Windows dialog we use the shell application object as indicated below, where “defaultFolder” is a variable that specifies the folder that appears first in the dialog. If not specified, it shows the Windows Desktop folder.

 

  Dim ShellApp As Object
  Set ShellApp = CreateObject("Shell.Application"). _
  BrowseForFolder(0, "Select a folder", 0, defaultFolder)

 

Then we can get the name and path of the folder as indicated below. If no folder is selected (cancel the dialog), the code below prompts an error, and therefore, we need to add error handling to avoid it.

 

  On Error Resume Next
  SelectedFolder = ShellApp.self.Path
  On Error GoTo 0

 

Finally, we clear the shell object and display the name of the folder (if selected). As explained earlier, there are different things we may want to do with that folder. We may want to loop through files, copy, move, etc. Check this other post to see how to loop through files in a folder: Get Info of Selected Files

    

  Set ShellApp = Nothing
  If SelectedFolder <> "" Then MsgBox SelectedFolder

 

And that’s how we can launch file dialogs with Excel VBA and get the name and path of the selected folder, file or files, and then open or save the file or files with the same property in some cases or using functions of the workbook object for other scenarios.