Determining the Path of a Workbook in Excel VBA

In many situations, it’s necessary to access other workbooks located in the same folder or in a subfolder of the current workbook. To do this reliably, you first need to determine the file path of the workbook that contains your VBA code (ThisWorkbook) or any currently active workbook.

The following example demonstrates how to retrieve the path for three different workbooks:

Sub DeterminePath()
    Workbooks.Open "C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv"
    MsgBox "Titanic-Dataset Mappel is located in: " & ActiveWorkbook.Path
    ActiveWorkbook.Close
    MsgBox "This Titanic-Dataset is located in: " & ThisWorkbook.Path
    Workbooks.Open ThisWorkbook.Path & "C:\Users\MAC 2015\Desktop\titanic.xls"
    MsgBox "The titanic Test is located in: " & ActiveWorkbook.Path
    ActiveWorkbook.Close
End Sub.

Explanation of the Procedure:

• Opening an external workbook and retrieving its path

• Open « C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv »
• MsgBox « Titanic-Dataset Mappel is located in:  » & ActiveWorkbook.Path

1. • This opens the file located at « C:\Users\MAC 2015\Desktop\Titanic-Dataset.csv ».
   • As it becomes the active workbook, the .Path property returns its directory path (C:\Users\MAC 2015\Desktop in this case).
   • The path is then shown in a message box.
   • The workbook is subsequently closed.

• Retrieving the path of the workbook that contains the macro

• MsgBox « This Titanic-Dataset is located in:  » & ThisWorkbook.Path

1. • ThisWorkbook refers specifically to the workbook containing the macro code, not necessarily the active workbook.
   • Its .Path property returns the folder in which this workbook is saved.
   • This is useful when you want your macro to refer to other files relative to its own location, regardless of which workbooks are currently active.

• Opening a file in a subfolder relative to the current workbook

• Open ThisWorkbook.Path & « C:\Users\MAC 2015\Desktop\titanic.xls »
• MsMsgBox « The titanic Test is located in:  » & ActiveWorkbook.Path

1. • Here, we use the path of ThisWorkbook and append \Subfolder\Test.xlsx to construct the full path to another file located in a subfolder named Subfolder.
   • After opening the file, the path is again displayed and the workbook is closed.

Practical Use Case:

This method is commonly used in VBA projects to ensure that files are accessed reliably, especially when deploying your Excel solution on different machines where absolute paths may not be consistent. By dynamically referencing paths based on ThisWorkbook.Path, your macros become portable and adaptable to various file system environments.