# How to get the active workbook location/path in Excel?

## Example 1: To get the active workbook location in excel by using the user defined formula:

### Step 1:

To understand the example and it’s working precisely consider the below given worksheet.

### Step 2:

In this step the user will be able to determine the location of cell by using the user-defined formula. Type the below given formula “=LEFT(CELL("filename",C2),FIND("[",CELL("filename",C2))-1)”. Consider the code snapshot to understand the processing of data.

### Explanation for formula:

• CELL("filename",C2) returns the full path of the current workbook, including the workbook name and extension.

• FIND("[",CELL("filename",C2)) finds the position of the opening square bracket ("[") in the file path. This is used to identify the start of the workbook name and exclude the path from the result.

• LEFT(CELL("filename",C2),FIND("[",CELL("filename",C2))-1) extracts the leftmost characters of the full path up to the position of the opening square bracket minus one character. This effectively removes the workbook name and extension, leaving only the path.

### Step 3:

The provided snapshot contains the full path of the current workbook.

### Step 4:

To obtain the required workbook location and workbook name, use the below provided formula −

=SUBSTITUTE( LEFT(CELL("filename",c2),FIND("]",CELL("filename",c2))-1),"[","")

Consider snapshot for reference −

Explanation for formula:

• The CELL function retrieves information about the cell containing the formula.

• The "filename" argument specifies that the function should return the full path and filename of the current workbook.

• The c2 argument tells the function to retrieve information about the cell in row 2 and column C.

• The LEFT function retrieves the leftmost characters from the returned filename, till a particular number of characters.

• The FIND function locates the position of the first occurrence of the "]" character within the extracted filename.

• The "-1" argument is used to adjust the position returned by the FIND function by subtracting one, so that the "]" character is not included in the result.

• The SUBSTITUTE function replaces the "[" character in the filename with an empty string, effectively removing it from the result.

## Example 2: To get the active workbook location in excel by using the VBA code:

### Step 1:

Use the same excel sheet, specified above. After that click on the Developer tab, and then under the “Code” section, click on “Visual Basic” option. This step will allow user to open a visual basic editor.

### Step 2:

The above step will open a “Microsoft Visual Basic for Application ” dialog box for user −

### Step 3:

Go to the Insert tab, after that choose the option named Module. Consider image snapshot for reference −

### Step 4:

Consider below given code −

' method name
Sub function_to_dis_wrk_path()
' display data to msgbox
MsgBox ActiveWorkbook.Path, vbInformation, "Workbook Path"
' end of sub module
End Sub


Please write code with proper indentation to avoid syntax errors. Complete snapshot for coding window is provided below −

### Step 5:

The above step will open the dialog box named “Macros”. After that select the name of the module and click on “Run” button. To obtain the generated results.

### Step 6:

The above step will display a dialog box, with the name “Workbook Path”. This dialog box contains a workbook path. Finally, click on “OK” button.

## Example 3: To get the active workbook location in excel by using the Kutools option:

### Step 1:

To insert the workbook information in excel sheet. Go to the “Kutools Plus” tab and choose “Workbooks & Sheets”. After that under the “Workbook” option, select the option for “Insert Workbook Information”.

### Step 2:

The above step will open a “Insert Workbook Information” dialog box. This option contains a label for “Worksheet name”, and at the “Insert at” label, in the range option add a valid range to display data. For this example, under the range label, add “A1” value. In the header section, select the option for “Left”. Finally, click on “OK” button. Consider below given snapshot for proper reference:

### Step 3:

The final displayed path is shown at the A1 cell.

## Conclusion

Sometimes, users forget to find the location of the active workbook. In this article, various user defined functions and VBA code are demonstrated in the examples to obtain the location of the active worksheet. Make sure that statements in the VBA code must be written carefully to run the program code successfully otherwise error may occur and may produce unwanted result.

Updated on: 22-May-2023

6K+ Views