VBA & Macro Problems

Extract a File Name or Folder Path in Excel VBA

Adam DavisAdam Davis Nov 13, 2026 869 views

Question details

The user needs a VBA function that returns either the file name or the folder portion of a complete Windows file path.

Extract a File Name or Folder Path in Excel VBA
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.
Before you start

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).

Solution 1Recommended

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.

1
Insert a standard module

In the VBA editor, select your workbook in Project Explorer, then choose Insert > Module.

2
Add the FilePath function

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

3
Test both return options

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.

4
Use the function in your workbook

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.

Create a VBA function to split a Windows file path
Separator and argument behavior: This function expects backslashes, not forward slashes. If there is no backslash, the File option returns the entire string and the Folder option returns an empty string. Use the exact arguments "File" and "Folder"; under VBA's default binary comparison, other capitalization or an unrecognized option returns an empty string.
Free Microsoft Office alternative

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.

Organize file names and folder-path lists in spreadsheets.Supports Microsoft Excel formats such as .xlsx and .xls; verify VBA support separately for macro-enabled workbooks.Familiar spreadsheet controls make moving from Excel easier.Free, lightweight tools for everyday spreadsheet work.
microsoft office alternative - wps office

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.