[Excel VBA] How to Show the “Open File” Dialog and Get the File Path (GetOpenFilename)

When reading external Excel files in macros, you often want the user to select the file manually instead of writing the file path directly in the code (hardcoding).

By using the Application.GetOpenFilename method in VBA, you can easily display the familiar “Open” dialog box and retrieve the full path of the selected file as a text string.

In this article, I will explain the basic usage of this method and how to set arguments to filter file types.

Completed VBA Code

Sub ShowFileOpenDialog()

    ' Declare variable. 
    ' Use Variant type because the return value is either a path (String) or False (Boolean).
    Dim selectedFilePath As Variant

    '--- 1. Display the "Open" dialog box ---
    selectedFilePath = Application.GetOpenFilename( _
        Title:="Please select the target Excel workbook", _
        FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm,All Files (*.*),*.*")
        
    '--- 2. Check the selection result ---
    If selectedFilePath = False Then
        ' When "Cancel" is pressed
        MsgBox "File selection was canceled."
    Else
        ' When a file is selected
        MsgBox "The following file was selected:" & vbCrLf & selectedFilePath
        
        ' (Advanced) Code to actually open the workbook
        ' Workbooks.Open selectedFilePath
    End If

End Sub

Key Points of the Code

1. Application.GetOpenFilename Method

When you run this method, the “Open” dialog box appears. The important point is that this method does not actually open the file. It simply returns the full path of the selected file as a string. To open the file, you must use Workbooks.Open separately using the retrieved path.

2. FileFilter Argument

This is a very important argument for setting the “File Type” dropdown list at the bottom right of the dialog.

Syntax: FileFilter:="Display Name 1,*.Extension1,Display Name 2,*.Extension2"

You specify pairs of “Display Name” and “Extension Pattern” separated by commas. If you want to group multiple extensions, separate them with a semicolon (;).

Example: "Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm"

• Excel Workbooks (*.xlsx; *.xlsm): The text displayed in the dropdown.
• *.xlsx;*.xlsm: The corresponding extension patterns.

3. Handling Cancellation

If selectedFilePath = False Then

If the user clicks the “Cancel” button without selecting a file, the GetOpenFilename method returns the Boolean value False. Therefore, you must declare the variable receiving the return value as a Variant type. By using an If statement to check if it is False, you can correctly handle the cancellation event.

Advanced: Opening the Selected File

By combining the file path obtained with GetOpenFilename and Workbooks.Open, you can complete the workflow of “opening the file selected by the user.”

Sub SelectAndOpenFile()
    Dim bookPath As Variant
    
    bookPath = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xlsx; *.xlsm),*.xlsx;*.xlsm")
    
    If bookPath <> False Then
        Workbooks.Open bookPath
        MsgBox ActiveWorkbook.Name & " has been opened."
    End If
End Sub

Summary

The Application.GetOpenFilename method is an essential feature for creating interactive VBA tools.

• The return value is the full file path (String); it does not open the file itself.
• Use the FileFilter argument to narrow down file types so users can choose easily.
• Make the variable receiving the return value a Variant type and check for False to detect cancellation.

By mastering these points, you can create more flexible and user-friendly VBA tools.