Please note javascript is required for full website functionality.
MVP

Blog

VBA: File Path Selector confined to CSV

21 June 2019

Welcome back to the VBA blog! This week we are going to expand on our blog from last week, relating to file path selectors.

 

Last week, we went over how to extract the file path of any other file and display it in a specific cell.

What if we wanted to set a restriction to the types of files that could be selected? For this example, we will limit the files that can be selected to be a csv file.

We have altered our code from last week:

 

Sub SelectFile()

Dim DialogBox As FileDialog

Dim path As String

 

Set DialogBox = Application.FileDialog(msoFileDialogFilePicker)

 

DialogBox.Title = "Select file for " & FileType

DialogBox.Filters.Add "Comma-Separated Value Files", "*.csv", 1

DialogBox.Show

 

If DialogBox.SelectedItems.Count = 1 Then

    path = DialogBox.SelectedItems(1)

End If

 

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

 

End Sub

 

The code remains the same as last week’s blog except for the changes in the Filters syntax. Let’s have a closer look at the code listed below:

DialogBox.Filters.Add "Comma-Separated Value Files", "*.csv", 1

This part of syntax confines the type of file selected only to CSV file as shown below. Other file types will not be able to be selected in this case.

After altering the code, we can use the same ‘File Selector’ button as before to run the macro:

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

This allows us to select a CSV file retrieve the file path to cell B2.

You can download an example file here.

 

Come back next week for more VBA.

Newsletter