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:
- Option Explicit
- Sub ReverseSearch()
- Dim sFolderPathwithFileName As String, sFileName As String
- Dim lngFileNamePosition As Long
- ''
- sFolderPathwithFileName = "C:\Users\Guest\Documents\Exercises\Intermediate.xlsx"
- lngFileNamePosition = InStrRev(sFolderPathwithFileName, "\")
- sFileName = Right(sFolderPathwithFileName, Len(sFolderPathwithFileName) - lngFileNamePosition)
- End Sub
Description:
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.