16. VBA Tips - Get A File Path

Nick's picture


Using VBA, there is a quick and easy way to obtain the file path of a file and return it to a sheet

  • Use GetOpenFileName
  • This brings up a dialog box that allows you to browse your files and choose a file
  • You can then use that file path how you want

Here's a screen shot of our setup in Excel:

get-a-file-path

When we press the button, we get this:

get-a-file-path

Once a file is selected, and "Open" is pressed, we can use the file path... in this case, we put it in a cell.

 

Here's the code we use to get the file path:

get-a-file-path

Explanation:

  1. GetOpenFilename allows you to enter the type of file you want to look for
  2. The Open File dialog box then appears with only the desired file types showing.. (In our example, we look for text files)
  3. When you choose one and press Open, it goes to a variable
  4. If you press cancel, GetOpenFilename returns FALSE, so you need to check for that
  5. You can use that file path as you want

Download sheet to practise how to Get A File Path in Excel

Training Video on how to Get A File Path in Excel:

AttachmentSize
get-a-file-path.xls34.5 KB

Get a path

Thanks so much! it was really helpful and useful for my job again thanks!!

Get A File Path

Thanks for the code really useful. Just starting VB so not really flowing yet :-) How can you adjust the code so that it doesn\\\'t enter to d6 but to the cell selected by the user on the sheeet

Many thanks

selection.value = filepath

selection.value = filepath

Getting just the folder

Great idea! How can this be modified so that the user can choose a folder and not a file? I need the macro to return a folder path only. Thanks!

try this: GetFolder

Sub GetFolder()

Set myFolder = Application.FileDialog(msoFileDialogFolderPicker)
With myFolder
.Title = "Choose Folder"
.AllowMultiSelect = False
If .Show <> -1 Then
Exit Sub
End If
FolderSelected = .SelectedItems(1)
End With

MsgBox "You Selected:" & FolderSelected

End Sub

Thanks. This help me so much.

Thanks. This help me so much.

Excellent! It help me a lot

Excellent! It help me a lot and saved me a lot of time