Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: File Path Selector

14 June 2019

Welcome back to the VBA blog! This week we are going to cover something we had to do for one of our consulting jobs.

 

While working on a project we had to extract the file path of a file and display it in a cell in Excel; an example of what a file path is:

C:\Users\SumProduct\Documents\Work\Excel Summit

 

Essentially we wanted the file path to appear in a cell like this:

This can be done reasonably easily, just by copying and pasting the file path from Window’s File Explorer.

However, how do we automate it so the client can click on a button, and browse for a file from File Open Dialog Box, then have that file path and file appear in cell B2?

First give cell B2 a range name of ‘File_Path’.

Next we step into VBA:

 

Sub SelectFile()

Dim DialogBox As FileDialog

Dim path As String

 

Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)

 

DialogBox.Title = "Select file for " & FileType

DialogBox.Filters.Clear

DialogBox.Show

 

If DialogBox.SelectedItems.Count = 1 Then

    path = DialogBox.SelectedItems(1)

End If

 

ThisWorkbook.Names("File_Path").RefersToRange.Value = path

 

End Sub

 

Let’s break down the code and explain what we did here, the first three lines of code:

Sub SelectFile()

Dim DialogBox As FileDialog

Dim path As String

 

starts the macro, and defines 2 variables we are going to use.

The next four lines:

Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)

 

DialogBox.Title = "Select file for " & FileType

DialogBox.Filters.Clear

DialogBox.Show

 

sets the DialogBox variable to use the File Open Dialog Box. The next few lines sets the title, clears filters, and displays the dialog box.

We had to include a line to clear filters, since the dialog box will inherit any other filters before.

The next three lines of code:

If DialogBox.SelectedItems.Count = 1 Then

    path = DialogBox.SelectedItems(1)

End If

 

is an IF loop, where VBA will check if we have selected a file, and when it does it will assign that file path to our ‘path’ variable.

The final 2 lines of code:

ThisWorkbook.Names("File_Path").RefersToRange.Value = path

 

End Sub

assigns the range name File_Path to equal our ‘path’ variable, then ‘End Sub’ ends the macro.

 

We can now create a Form Control Button and assign a macro to it:

Clicking on the ‘File Selector’ button will bring up our dialog box: 

Allowing us to select a file, and Excel will retrieve the file path.

You can download an example file here.

 

Come back next week and we’ll expand on this macro. 

Newsletter