Extract a File Name or Folder Path in Excel VBA
Question details
The user needs a VBA function that returns either the file name or the folder portion of a complete Windows file path.

- Product
- Microsoft Excel VBA
- Device & OS
- Windows
- Scenario
- Extract a workbook file name or its containing folder from a path such as C:\Reports\Budget.xlsx.
- Observed behavior
- The function should return Budget.xlsx when asked for the file name, or C:\Reports\ when asked for the folder path.
Open your workbook in desktop Excel for Windows and press Alt+F11 to access the VBA editor. If you want to keep the function in the workbook, save it as an Excel Macro-Enabled Workbook (*.xlsm).
Create a VBA function to split a Windows file path
Use this compact function when you already have a path string and need either its file name or folder portion.
InStrRev locates the last backslash in the path. Mid returns the characters after that separator, while Left returns the folder portion, including the trailing backslash. This extracts text only; it does not check whether the file exists.
In the VBA editor, select your workbook in Project Explorer, then choose Insert > Module.
Paste this code into the module: Function FilePath(pathString As String, turnType As String) As String Dim p As Long p = InStrRev(pathString, "\") If turnType = "File" Then FilePath = Mid(pathString, p + 1) ElseIf turnType = "Folder" Then FilePath = Left(pathString, p) End If End Function
Press Ctrl+G in the VBA editor to open the Immediate window. Enter each command and press Enter: ? FilePath("C:\Reports\Budget.xlsx", "File") ? FilePath("C:\Reports\Budget.xlsx", "Folder") The results are Budget.xlsx and C:\Reports\, respectively.
Return to Excel, enter a complete path in cell A1, and use =FilePath(A1,"File") or =FilePath(A1,"Folder") in another cell. Save the workbook as *.xlsm to retain the VBA code; macro execution must be permitted by your security settings.

Work with Excel Files and Path Lists in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for easy migration. Use WPS Spreadsheets to organize workbook paths and review spreadsheet data. VBA execution depends on your WPS edition and platform, so verify support before moving a workbook that relies on this function.

Frequently Asked Questions
Does the returned folder path include the final backslash?
Yes. Left(pathString, p) includes the backslash found by InStrRev, so C:\Reports\Budget.xlsx returns C:\Reports\. To exclude it, use Left(pathString, p - 1) only when p > 0.
What happens if I pass only a file name?
For FilePath("Budget.xlsx", "File"), InStrRev returns 0 and Mid starts at character 1, returning Budget.xlsx. The Folder option returns an empty string because there is no folder separator.
Can I extract the name and folder of the workbook containing the code?
Yes. In a VBA procedure or the Immediate window, use FilePath(ThisWorkbook.FullName, "File") or FilePath(ThisWorkbook.FullName, "Folder"). Save the workbook first so it has a complete file-system path; an unsaved workbook does not have a saved folder path.
Will this work with network paths or web URLs?
It works with backslash-based network paths such as \\Server\Share\Budget.xlsx because it searches for the final backslash. It does not split forward-slash URLs. For simple forward-slash path strings, change the separator in InStrRev to "/"; URLs with query strings need additional parsing.
Why does Excel show #NAME? when I use FilePath in a cell?
Press Alt+F11 and confirm that the function is in a standard module created through Insert > Module, rather than a worksheet or ThisWorkbook module. Also check that the workbook containing the function is open, saved as *.xlsm, and allowed to run VBA under your organization's security policy.




