Excel VBA: Get the Current Workbook File Path Easily

In Excel VBA, use ThisWorkbook.Path to get the folder path of the workbook that contains the code, or ActiveWorkbook.Path to get the folder path of the workbook currently active in Excel. To get the complete path including the file name, use that workbook’s FullName property. If the workbook has never been saved, its Path is an empty string.

Get a workbook’s folder path

For a macro stored in the workbook whose location you need, ThisWorkbook.Path is usually the clearest choice. It returns the folder location, not the workbook’s name. For example, if a workbook is saved in a Windows folder such as C:\Reports, the value is the folder path rather than a string that ends in the workbook’s file name.

Before running a macro that reads the path, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, and a macro may later be expanded to write, move, or delete files. The following example only displays the folder path:

Sub ShowThisWorkbookPath()
    Dim wb As Workbook

    ' ThisWorkbook is the workbook that contains this VBA code.
    Set wb = ThisWorkbook

    If Len(wb.Path) = 0 Then
        MsgBox "Save this workbook before reading its folder path."
        Exit Sub
    End If

    MsgBox wb.Path
End Sub

When you insert this code in a workbook module or a standard module inside the workbook, ThisWorkbook refers to that file. The result is useful for building a destination folder, showing users where a report is stored, or checking whether a workbook has been saved before a procedure continues.

Choose between ThisWorkbook and ActiveWorkbook

The phrase “current workbook” can mean two different things in Excel. ThisWorkbook is the file where the running VBA code is stored. ActiveWorkbook is the workbook window that is currently active. They can refer to different files.

This difference matters when code runs from Personal.xlsb, an add-in, or another controller workbook. For example, a macro stored in Personal.xlsb may be used while a report is active. In that situation, ThisWorkbook.Path points to Personal.xlsb’s folder, while ActiveWorkbook.Path points to the report’s folder. Pick the reference based on which file you actually mean, rather than relying on the word “current.”

Use the active workbook when the user’s selected workbook is the intended target. A small check avoids an error if Excel has no active workbook:

Sub ShowActiveWorkbookPath()
    Dim wb As Workbook

    ' The active workbook can differ from the workbook holding the code.
    Set wb = ActiveWorkbook

    If wb Is Nothing Then
        MsgBox "There is no active workbook."
        Exit Sub
    End If

    If Len(wb.Path) = 0 Then
        MsgBox "Save the active workbook before using its folder path."
        Exit Sub
    End If

    MsgBox wb.Path
End Sub

For automation that opens several workbooks, an even safer approach is to keep a reference to the particular workbook returned by Workbooks.Open and use that variable. The active window can change during a procedure, so it is not always a reliable way to identify a specific file.

Get the complete file name and path

Path and FullName answer different questions. Path gives the workbook’s folder. FullName gives the full workbook name and location together, including the extension, such as C:\Reports\Quarterly.xlsx. If you need to display the exact workbook file, record it in a log, or pass it to code that expects a full file name, use FullName.

For example, with a workbook reference named wb, the relevant properties are wb.Path, wb.Name, and wb.FullName. Name is only the file name, such as Quarterly.xlsx; it does not include the folder. Using the right property helps prevent mistakes such as trying to open a folder as though it were a file.

If the goal is simply to identify the currently active workbook, the full path can be displayed directly with ActiveWorkbook.FullName, after confirming that an active workbook exists and has been saved. You do not need to assemble a full path by joining the folder and name yourself.

Handle a workbook that has not been saved

A new workbook may have a visible name such as Book1, but that does not mean it has a disk location. Until it is saved, Workbook.Path is an empty string. Code that assumes the property always contains a folder can create invalid file names, fail when opening or saving a file, or silently construct a path in the wrong place.

Check the path before using it, as in the earlier examples. If the workbook needs a location, let the user save it first, or use Excel’s Save As workflow. Saving assigns the workbook a location; after that, read the property again rather than assuming a location that was chosen earlier is still current. A workbook can be saved under a new name or moved with Save As, changing its path.

Also distinguish an unsaved workbook from a workbook whose path is unavailable or represented differently because it is stored online. An empty path is a useful signal to stop and handle the situation deliberately; it is not a folder name that should be concatenated with a file name.

Build a path for another file safely

If you need a sibling file in the workbook’s folder, start with the workbook’s path and add a separator and the desired file name. On Windows, the separator is normally a backslash. VBA’s Application.PathSeparator provides the separator Excel reports for the current environment, which is preferable to hard-coding a Windows backslash when the workbook may be used on a Mac.

For example, after confirming that wb.Path is not empty, a procedure can construct a local sibling file name with wb.Path & Application.PathSeparator & "Summary.csv". This creates a path string; it does not create the file, confirm that the folder exists, or guarantee that the file can be written. Before saving output, consider whether the destination already exists and whether the user has permission to write there.

Do not add a separator if you are already using wb.FullName, because that property already includes the file name. Likewise, avoid joining a folder and file name when the intended target is the workbook itself: pass wb.FullName to code that needs the workbook’s full file name.

OneDrive, SharePoint, and other online locations

A workbook opened from OneDrive or SharePoint may be associated with a cloud location, a synchronized local folder, or an online URL, depending on how it was opened and how the organization is configured. Therefore, do not assume every path returned by Excel is a conventional local Windows folder that another program can access as a disk path. The value and the way it can be used depend on the workbook’s storage and access route.

Inspect the returned string before passing it to file-system operations such as opening a local file or creating a folder. A URL and a local synchronized path are not interchangeable. If the macro must save output beside a cloud-hosted workbook, test the intended workflow in the actual storage setup and use an approach supported by that location. For portable code, avoid assuming that a cloud workbook will always have a fixed local sync path on every user’s computer.

Common path errors and how to avoid them

• Reading the wrong workbook: A macro in an add-in or Personal.xlsb may make ThisWorkbook refer to the code container. Use a specifically captured workbook reference or ActiveWorkbook when the active file is truly the target.
• Using an empty folder path: Check Len(wb.Path) before building a destination. A never-saved workbook has no folder path.
• Confusing folder and file: Choose Path for the folder and FullName for the complete file path. Choose Name for the file name alone.
• Assuming a trailing separator: Do not depend on Path ending with a slash. Add Application.PathSeparator when joining a local folder and a file name.
• Assuming every location is local: Cloud URLs and synchronized local paths can behave differently. Check the actual value and the requirements of the file operation.
• Relying on the active window throughout a long macro: Opening or switching workbooks can change which workbook is active. Store the intended workbook in an object variable and use it consistently.

To open the Visual Basic Editor on Windows, press Alt+F11; on Mac, the usual shortcut is Option+F11, and some keyboards also require Fn. You can also use the Visual Basic command on Excel’s Developer tab. The Developer tab may need to be enabled in Excel’s ribbon settings. Excel for the web does not run VBA macros; open the workbook in the desktop Excel application to use this code.

Frequently Asked Questions

How do I get the current workbook’s folder in VBA?
Use ThisWorkbook.Path for the folder of the workbook containing the macro. Use ActiveWorkbook.Path if you mean the workbook currently active in Excel and have checked that it exists and has been saved.

How do I get the full file path, including the file name?
Use the appropriate workbook reference followed by .FullName, such as ThisWorkbook.FullName. It includes the workbook’s name and extension as well as its location.

Why is Workbook.Path blank?
The workbook has probably not been saved to a location yet. Save it, then read Path again. Do not treat an empty value as a valid folder.

Does ThisWorkbook mean the workbook I am looking at?
Not necessarily. It means the workbook that contains the VBA code. The workbook you are looking at is usually represented by ActiveWorkbook, but those references can point to different files.

Does Path include the workbook’s file name?
No. Path is the folder location; FullName includes the folder and file name. Name returns the file name without its folder.

Will the same VBA path work in Excel for the web?
No. Excel for the web does not execute VBA. Run the macro in desktop Excel. Also verify cloud-storage paths in the desktop environment where the workbook is opened, because an online location and a synchronized local folder may not be interchangeable.

Related

• Excel Page Breaks Keep Appearing? How to Stop Them
• Excel Page Break View Without Page Numbers: What to Do
• Excel Page Break Shortcut: Insert, Remove, and Reset

More in VBA & macros

• Excel VBA MsgBox New Line: Add Line Breaks CorrectlyTo put a new line in an Excel VBA message box, join the text with vbCrLf: MsgBox "First…
• Excel VBA: Delete a Row and Shift UpTo delete a complete worksheet row in Excel VBA and move the rows below it up, use Rows(rowNumber).Delete,…
• Excel VBA: Select a Range Using VariablesIn Excel VBA, use variables inside Cells(row, column) and pass the two cell references to Range to build…
• Excel VBA: Find the Minimum Value in a Range or ArrayTo find a range’s minimum in Excel VBA, use Application.WorksheetFunction.Min, for example, Application.WorksheetFunction.Min(Range("B2:B20")). For a macro you can…
• VBA Loop Example: For, Do While, and For Each LoopsA VBA loop repeats a block of code, either a set number of times or until a condition…
• VBA MsgBox New Line: Add Line Breaks to MessagesTo add a new line in a VBA MsgBox, join the message text with vbCrLf or vbNewLine where…

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.