CODEWITHSUNDEEP
excelfilenamesvba
Tyler
Tyler

Reputation: 45

Excel Vba - Pull File Name from File Path

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

Upvotes: 0

Views: 6260

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

Related Questions