Excel File Picker VBA

Choosing a file with as an Excel file picker can be useful if you want to be able to pick a specific file to edit in a VBA procedure. The process is can be used when you want human interaction and there is no way to know the file name for the procedure in advance. In this case the person that runs the process will have a file picker open and they can choose the file they need to open during the VBA process.

The most critical part about the choose file VBA process is the file path your users are going to choose from. The path should be in existence. In the following example we have included the file path on the desktop.

Path = "C:\Users\Desktop\"  

This is declared with a constant however, if you wanted to declare the variable with a cell reference just change out this line with the following two lines.

Out

Const Path = "C:\Users\Desktop\"   'Change to suit

New Lines

Dim Path As String
Path = [A1].Value

Where cell A1 has the file path. When the procedure is run my desktop should open.

The following is the VBA code to run the file picker procedure.

Option Explicit

Sub ChooseFile()
Const Path = "C:\Users\Desktop\"   'Change to suit
Dim fd As FileDialog
Dim fName As String 
Dim i As Integer

Set fd = Application.FileDialog(msoFileDialogFilePicker)

    fd.Title = "Select the XL File"
    fd.InitialFileName = Path
    i = fd.Show
    fd.Filters.Clear
    fd.Filters.Add "Excel files", "*.xls*"
         If i <> -1 Then
             MsgBox "You Cancelled, try again later"
         Else
            fName = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))
        End If
    Set wb = Workbooks.Open(Path & fName)
End Sub

If the user chooses to cancel the VBA procedure part way through then there is a trap in the code to cover this possibility.  The code will stop and the user will be allowed to exit the VBA process.

This VBA procedure will allow you to have a dialog open in Excel and you can choose the file which will open. This type of procedure is perfect where the path is known but the file to choose is not. Hope this helps.