Browse File And Assign Full Path And Name Into Two Variables

This Excel VBA tutorial explains how to use file browsers in Excel VBA applications. In Excel VBA applications, we often need to let the user input data from various files to the application. If we know the file path then we can directly use that path in the code to open the file. But this method is not practicable because we can’t ask the users to open the VBA editor and change the codes. Also it will be impossible if you lock the source code. Therefore using a file browser is the ideal solution for that kind of requirement.

If the application has a file browser option like above, then users can easily select files to upload the data. So let’s look at how to add a file browser option to a VBA application. Let’s name this subroutine as “SelectFile”

Sub SelectFile()

End Sub

First we need to declare a few variables.

Dim strFile As String
Dim FullPath As String
Dim FileName As String
Dim WrdArray() As String

Next we can show the “Open dialog box”. So the user can select a file.

sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

Now we need to check whether the user clicked on the “Open” button or the “Cancel” button. We can use a If .. Then .. Else Statement to determine that.

If sFile = "False" Then
     'If user click "Cancel" button
Else

End If

If the user clicks on the “Cancel” button then we should exit the subroutine using “Exit Sub” statement. Otherwise the program will be executed to the file opening section and produce an error. If the user clicks on the “Open” button, sFile will contain the full path of the file. So we can assign it to the FullPath variable as follows.

FullPath = sFile

Now the remaining task is to extract the name of the file from the sFile variable. We can use the Split function to get that job done. Name of the file will occur after the last “\” character. So we can split the sFile string using “\” as a delimiter and then get the last element of the result array.

WrdArray() = Split(sFile, "\")
FileName = WrdArray(UBound(WrdArray()))

Want to learn about Split function? Read this post => Split Function

Now complete If .. Then .. Else block should look like this.

If sFile = "False" Then
     'If user click "Cancel" button
     Exit Sub
Else
     FullPath = sFile
     WrdArray() = Split(sFile, "\")
     FileName = WrdArray(UBound(WrdArray()))
End If

Finally we can open the file and assign it to the WB variable as follows.

Set WB = Workbooks.Open(FullPath)

Note that the full path is enough to open the file. But knowing the file name can be useful for some other purposes. Below is the full code for the “SelectFile” subroutine.

Sub SelectFile()

    Dim WB As Workbook
    Dim strFile As String
    Dim FullPath As String
    Dim FileName As String
    Dim WrdArray() As String

    sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")

    If sFile = "False" Then
         'If user click "Cancel" button
         Exit Sub
    Else
         FullPath = sFile
         WrdArray() = Split(sFile, "\")
         FileName = WrdArray(UBound(WrdArray()))
    End If

    Set WB = Workbooks.Open(FullPath)

End Sub

In the above example we let the user to only select the Excel files. If you want to allow the user to select any file type then replace “sFile = Application.GetOpenFilename("Excel workbooks,*.xls*")” with below line.

sFile = Application.GetOpenFilename()

And sometimes you may want to permit users to only select CSV files. Use the below code in that case.

sFile = Application.GetOpenFilename("CSV files,*.csv")

In this lesson we considered selecting only one file. But you can use the “GetOpenFilename” method to select multiple files as well. But then it will return an array (array will contain names of the selected files.) instead of a string.