Vba extract file name from full path in Excel

For example, I have the full path to a file (C:\Users\Guest\Documents\Exercises\Intermediate.xlsx) - I want to extract just the file name, "Intermediate.xlsx".

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub ReverseSearch()
  3. Dim sFolderPathwithFileName As String, sFileName As String
  4. Dim lngFileNamePosition As Long
  5. ''
  6. sFolderPathwithFileName = "C:\Users\Guest\Documents\Exercises\Intermediate.xlsx"
  7. lngFileNamePosition = InStrRev(sFolderPathwithFileName, "\")
  8. sFileName = Right(sFolderPathwithFileName, Len(sFolderPathwithFileName) - lngFileNamePosition)
  9. End Sub


a) This is a classic example of searching a string in reverse direction. Excel VBA has an inbuilt function "InStrRev" to determine the position of a "string" when searched from right to left.

b) Row 7 is used to determine the position of the last occurrence (rightmost) of "\". Any text to the right of the rightmost "\" is file name.

c) In row 8, text to the right of the rightmost "\" is extracted.


You can find similar Excel Questions and Answer hereunder

1) Vlookup to return max value from multiple hits in Excel

2) How can I protect / unprotect WorkSheet using VBA?

3) Write to text file without quotes in vba in Excel

4) How can I hide Formula Bar and Headings using VBA?

5) Line break in vba message box in Excel

6) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

7) How can I loop through all WorkSheets and get their names using VBA?

8) How can I delete all shapes in a WorkSheet?

9) How to create Pivot table in excel VBA

10) How can I change the Marker size and Marker line color for all the series in a chart?


Here the previous and next chapter