Below is a demonstration of using Application.FileDialog to give the user an option to select a file. The code is well commented and should be self explanatory.
The full file path will be stored in fullpath variable, which can be used later in the code.
An example using the code to prompt the user to select an Excel file an open it in Excel can be found here http://www.chicagocomputerclasses.com/excel-vba-display-a-file-open-dialog-and-open-the-file-excel-functions/
'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)
Posted by Excel Instructor: