3 Effective Methods to Get the Path of the Current Excel Workbook

When you are working on an Excel workbook, you may need to refer to the path of this file. In this article, we will show you 3 methods to get the path.

Getting the path of the current workbook seems to be very easy. However, if you close the folder and couldn’t remember the exact location, you will find it hard to get the path especially, especially when there are many files in your computer. You can certainly use the search feature in your computer.

But if there are many files in your computer, you need to spend a lot of time on the search process. On the other hand, if there are many Excel files with the same name in different paths, you will still need to select them one by one. In order to get the path quickly, you can refer to the 3 methods below.

Method 1: Check Workbook Information

This method is very easy. Just click the tab “File” in the ribbon and you can get the information.

Here you will know the complete path of the current workbook. And then you can get access to the path easily.

Method 2: Use Excel Functions

You can only know the exact location of the path of current workbook. If you need to use the path in a worksheet, you can use Excel functions.

1. Click the target cell in the worksheet.
2. Now input the following formula into this cell”

=CELL(“filename”)

3. And then press the button “Enter” on the keyboard.

After that, you will see the path in the cell. Except for the path and the name of the current file, you can also know the active sheet.

On the other hand, when you don’t need the sheet name, you can also change the formula.

= LEFT(CELL(“filename”),FIND(“]”,CELL(“filename”)))

This formula will get the path to the name of the workbook.

In addition, you can also get only the exact path without the name of the workbook. And the formula will be this:

=LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-2)

When you press “Enter” on the keyboard, you will see the result immediately.

You can choose one of the formulas according to your actual need. When you need to use the path, you can copy it and paste as values. Therefore, only the path will leave in the target cell.

Method 3: Use VBA Macros

Except for the above two methods, you can also use VBA macros to finish this task.

1. Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
2. And then click the tab “Insert” in the toolbar.
3. After that, choose the option “Module” in the sub menu.
4. In this step, inputting the following codes into the new module:

'get only the path
Sub GetPath1()
  Range("B1") = ActiveWorkbook.Path
End Sub

'get the path with the file name
Sub GetPath2()
  Range("B2") = ActiveWorkbook.FullName
End Sub

'get the path with the file name and the sheet name
Sub GetPath3()
  Range("B3") = ActiveWorkbook.FullName & "," & ActiveSheet.Name
End Sub

There are three different macros in the codes. And for different purpose, the codes will be a little different. The first one will only get the path. The second one will get the path with the file name. And the last one will also get the worksheet name. You may also change the target cell in the codes according to your need.

5. When you need to get the path, you can run the macro by clicking the button “Run Sub” or press the button “F5” on the keyboard. Thus, the result will appear in the designated cell.

A Comparison between Those Methods

In this table below, we have input the result of the comparison of the three methods.

Now you must have a comprehensive understanding of the three methods. And the next time, you can choose the most suitable methods according to your need.

Avoid Data Disaster is Next to Impossible

If you need to use Excel to finish your work, you will certainly deal with a lot of data and information. Therefore, you will sometimes meet with data disaster. And it is impossible that anyone can fully avoid the occurrence of data disaster. When Excel corruption happens, the only thing you can do is to repair it immediately. And in order to repair Excel xlsx file, you can use a third-party tool. This tool is capable of repairing most of the problems in Excel. Therefore, even though you cannot avoid data disaster, you will never lose your data.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word document damage and outlook repair software products. For more information visit www.datanumen.com