vba extract file name from full path in Excel

Q152. In Excel, how can I extract file name from a full path including folder path and file name?

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

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.

 

You can find similar Excel Questions and Answer hereunder

1) Vba clear the contents of an entire sheet in Excel

2) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

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

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

5) How can I avoid updates to cell values during macro execution?

6) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?

7) How can I set the Source Data of charts using VBA?

8) How can I clear cell after activating a routine when there is a change in value of a cell?

9) How can I get users to select a file for processing using my macro?

10) How can worksheet functions be accessed in VBA?

11) Calling a macro from another workbook in Excel

12) How can I get the count of number of series in a Chart using VBA?

13) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

14) How can I find the last used cell in a Column in VBA?

15) Vba list all files in a folder in Excel