180

I wrote a macro as an add-in, and I need to get the path of the current worksheet on which it is being executed. How do I do this? How do I get the file path (just the directory)?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • 2
    do you mean that, given a worksheet, you want to know the folder where the parent workbook is saved? – Mathias May 11 '10 at 19:53

5 Answers5

338

Use Application.ActiveWorkbook.Path for just the path itself (without the workbook name) or Application.ActiveWorkbook.FullName for the path with the workbook name.

BradC
  • 39,306
  • 13
  • 73
  • 89
  • 45
    Activeworkbook depends on which workbook is active. USe Thisworkbook.path – Alwyn Miranda Feb 29 '16 at 10:46
  • 9
    Both are useful in different situations, of course. I took the original question to mean that the macro resided in an add-in workbook (which would be ThisWorkbook.path), but needed to run code against other workbooks as needed by the user (which would be ActiveWorkbook.path). – BradC Mar 03 '16 at 14:40
  • 4
    Should always be explicit - if it's this workbook, it should be application.thisworkbook.path. If it's a workbook being opened, the name should be defined with a set, then application.Variablename.path (or fullpath, depending). – Selkie Oct 19 '17 at 22:36
  • 1
    @Selkie If the *code itself* opens the workbook, then sure. But if this is simply a helper macro that resides in an add-in workbook and needs to operate on whichever workbook the user currently has open when they invoke the macro, then `ActiveWorkbook` is what is needed. – BradC Oct 20 '17 at 13:30
  • 6
    `Excel.ActiveWorkbook.Path` only works if the file has been saved at least once. Also, if the file has never been saved, `Excel.ActiveWorkbook.FullName` only returns the file name. Might be a good idea to [check if the workbook has ever been saved](https://stackoverflow.com/questions/51530313/can-vba-determine-if-an-excel-workbook-has-ever-been-saved). – ChrisB Mar 22 '19 at 01:51
  • Note that if the Workbook is saved on OneDrive or inside a directory synchronized by OneDrive, this solution will return a URL, not the local path of the Workbook. A solution to get the local path on this case is presented in [this answer](https://stackoverflow.com/a/73577057/12287457). – GWD Nov 11 '22 at 20:48
41

Always nice to have: 

Dim myPath As String     
Dim folderPath As String 

folderPath = Application.ActiveWorkbook.Path    
myPath = Application.ActiveWorkbook.FullName
psubsee2003
  • 8,563
  • 8
  • 61
  • 79
Alex22
  • 519
  • 5
  • 5
36

If you want to get the path of the workbook from where the macro is being executed - use

Application.ThisWorkbook.Path

Application.ActiveWorkbook.Path can sometimes produce unexpected results (e.g. if your macro switches between multiple workbooks).

Gangula
  • 5,193
  • 4
  • 30
  • 59
avalanche1
  • 3,154
  • 1
  • 31
  • 38
  • @avalanche1.That is totally correct, activeworkbook raised alot of unexpected results with me – Leedo Oct 24 '21 at 15:43
4

The quickest way

path = ThisWorkbook.Path & "\"
Pablo Vilas
  • 546
  • 5
  • 13
0

I had the same problem and I built a solution that I'm going to share. Below is the function in VBA for Excel GetLocalPath(), which gets the local path of the ActiveWorkbook:

`Function GetLocalPath() As String

Dim sRowPath    As String
Dim sLocalPath  As String
Dim iFindhttp   As Integer

sRowPath = Application.ActiveWorkbook.Path

If LCase(Left(sRowPath, 4)) = "http" Then
    Dim fso As New FileSystemObject
    sLocalPath = fso.GetAbsolutePathName(sRowPath)
    iFindhttp = InStr(LCase(sLocalPath), "\http")
    sLocalPath = Left(sLocalPath, iFindhttp - 1)
    Set fso = Nothing
Else
    sLocalPath = sRowPath
End If
        
GetLocalPath = sLocalPath

End Function`

Julio Garcia
  • 1
  • 1
  • 3
  • 1
    Hi @JulioGracia, the problem you are addressing is how to convert a OneDrive Url to a local path. Unfortunately, your function doesn't work for any of the test cases I set up for such functions. If you are interested in a better solution for this problem, take a look at [this solution](https://stackoverflow.com/a/73577057/12287457). – GWD Nov 11 '22 at 00:08
  • thanks for your comment, it was usefull for me, and I confirm the solution of Julio didn't work in my case, but the solution mentionned in the link that you shared works fine till now – JustGreat Feb 08 '23 at 16:07