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.
'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
'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
'Store in fullpath variable
fullpath = .SelectedItems.Item(1)
'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
'Open the file selected by the user
Posted by Excel Instructor: