Excel Vba - Pull File Name from File Path

Question

My code below is used to select a file and load the file path into the textbox. I am trying to extract just the file name from this and put that in the textbox. I am sure there is a simple way to do this, but I cannot find out how. Thank you for any help!

Private Sub openDialog1()
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

  .AllowMultiSelect = False

  .Title = "Please select the report."

  .Filters.Clear
  .Filters.Add "Excel 2003", "*.xls"
  .Filters.Add "All Files", "*.*"

  If .Show = True Then
    TextBox1 = .SelectedItems(1)

  End If
End With
End Sub

Somehow did not find this in my searching, I'll see if I can work it out from this. — Tyler
– Tyler, Commented Feb 17, 2015 at 15:39
Cool, give it a try (I'd suggest using the FileSystemObject method in the second answer). — Gareth
– Gareth, Commented Feb 17, 2015 at 15:42
I got it to work with the FileSystemObjectMethod, but do I need to select the Tools> references >Microsoft Scripting Runtime feature on every computer I run this code on? If that is so then I cannot use it. — Tyler
– Tyler, Commented Feb 17, 2015 at 15:52
No, you only need to load Microsoft Scripting Runtime feature to the workbook with the code. — Mitch
– Mitch, Commented Feb 17, 2015 at 17:51

Gary's Student · Accepted Answer · 2015-02-17 16:19:13Z

2

You just need to discard the path part. This will display the filename.ext

Sub openDialog1()
Dim fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

  .AllowMultiSelect = False

  .Title = "Please select the report."

  .Filters.Clear
  .Filters.Add "Excel 2003", "*.xls"
  .Filters.Add "All Files", "*.*"

  If .Show = True Then
    ary = Split(.SelectedItems(1), "\")
    MsgBox ary(UBound(ary))
  End If
End With
End Sub

answered Feb 17, 2015 at 16:19

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Over a year ago

Your solution was effective and much less complicated than the 'FileSystemObject' method. Thank you.

Collectives™ on Stack Overflow

Excel Vba - Pull File Name from File Path

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related