Below is a demonstration of using Application.FileDialog to give the user an option to select a file and then open it. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which is used later in the code to open the file after making sure there was an Excel file selected.

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With
    
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    Workbooks.Open fullpath

End Sub

 

Posted by Excel Instructor:

https://www.chicagocomputerclasses.com/excel-classes/