- MS Excel Basics
- Excel - Home
- Excel - Getting Started
- Excel - Explore Window
- Excel - Backstage
- Excel - Entering Values
- Excel - Move Around
- Excel - Save Workbook
- Excel - Create Worksheet
- Excel - Copy Worksheet
- Excel - Hiding Worksheet
- Excel - Delete Worksheet
- Excel - Close Workbook
- Excel - Open Workbook
- Excel - Context Help
- Editing Worksheet
- Excel - Insert Data
- Excel - Select Data
- Excel - Delete Data
- Excel - Move Data
- Excel - Rows & Columns
- Excel - Copy & Paste
- Excel - Find & Replace
- Excel - Spell Check
- Excel - Zoom In-Out
- Excel - Special Symbols
- Excel - Insert Comments
- Excel - Add Text Box
- Excel - Undo Changes
- Formatting Cells
- Excel - Setting Cell Type
- Excel - Setting Fonts
- Excel - Text Decoration
- Excel - Rotate Cells
- Excel - Setting Colors
- Excel - Text Alignments
- Excel - Merge & Wrap
- Excel - Borders and Shades
- Excel - Apply Formatting
- Formatting Worksheets
- Excel - Sheet Options
- Excel - Adjust Margins
- Excel - Page Orientation
- Excel - Header and Footer
- Excel - Insert Page Breaks
- Excel - Set Background
- Excel - Freeze Panes
- Excel - Conditional Format
- Working with Formula
- Excel - Creating Formulas
- Excel - Copying Formulas
- Excel - Formula Reference
- Excel - Using Functions
- Excel - Builtin Functions
- Advanced Operations
- Excel - Data Filtering
- Excel - Data Sorting
- Excel - Using Ranges
- Excel - Data Validation
- Excel - Using Styles
- Excel - Using Themes
- Excel - Using Templates
- Excel - Using Macros
- Excel - Adding Graphics
- Excel - Cross Referencing
- Excel - Printing Worksheets
- Excel - Email Workbooks
- Excel- Translate Worksheet
- Excel - Workbook Security
- Excel - Data Tables
- Excel - Pivot Tables
- Excel - Simple Charts
- Excel - Pivot Charts
- Excel - Keyboard Shortcuts
- MS Excel Resources
- Excel - Quick Guide
- Excel - Useful Resources
- Excel - Discussion
How to get the active workbook location/path in Excel?
In this article, the user will learn the process of getting the active worksheet location/path in excel by using the three possible methods. This article contains three examples. The first example is based on the user defined formula. While the second example is based on the use of VBA code, and finally third example makes use of kutool extension. All the three examples use the same excel sheet, to display the processing. The provided three examples will precisely perform the same task but use different approaches. The main purpose of this article is to understand the available different type of approach to perform the same task.
Example 1: To get the active workbook location in excel by using the user defined formula:
To understand the example and it’s working precisely consider the below given worksheet.
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.
The provided snapshot contains the full path of the current workbook.
To obtain the required workbook location and workbook name, use the below provided formula −
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:
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.
The above step will open a “Microsoft Visual Basic for Application ” dialog box for user −
Go to the Insert tab, after that choose the option named Module. Consider image snapshot for reference −
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 −
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.
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:
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”.
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:
The final displayed path is shown at the A1 cell.
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.
- Related Articles
- How to get the current workbook name in Excel?
- How to get the active sheet in a workbook in Selenium with python?
- How to encrypt the MS excel workbook?
- How to Always Search the Whole Workbook in Excel?
- How to Apply a Button to Open Another Workbook in Excel?
- How to Always Start the Same Worksheet When Opening a Workbook in Excel?
- How to Copy a Selected Range to a New Workbook in Excel?
- How to Apply a Template to an Existing Chart/Workbook in Excel?
- How to hide columns on multiple sheets in a workbook in Excel?
- How to test the shared location path from the remote computer in PowerShell?
- How to get device location in android?
- How to clear filters when opening, saving or closing workbook in Excel?
- How To Check If a Worksheet Or Workbook Is Protected In Excel?
- How to Automatically Protect All Worksheets When Closing an Excel Workbook?
- How to Copy Current Cell Address to Other Location in Excel?