VBA Blogs: 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.
