176

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,816
  • 57
  • 73
  • 92
Alex Gordon
  • 54,010
  • 276
  • 644
  • 1,024
  • 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

4 Answers4

324

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
  • 38,476
  • 12
  • 69
  • 90
  • 40
    Activeworkbook depends on which workbook is active. USe Thisworkbook.path – Alwyn Miranda Feb 29 '16 at 10:46
  • 8
    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
  • 4
    `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
40

Always nice to have: 

Dim myPath As String     
Dim folderPath As String 

folderPath = Application.ActiveWorkbook.Path    
myPath = Application.ActiveWorkbook.FullName
psubsee2003
  • 8,395
  • 8
  • 60
  • 77
Alex22
  • 509
  • 5
  • 5
32

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
  • 3,484
  • 2
  • 19
  • 41
avalanche1
  • 2,123
  • 1
  • 23
  • 32
  • @avalanche1.That is totally correct, activeworkbook raised alot of unexpected results with me – Leedo Oct 24 '21 at 15:43
3

The quickest way

path = ThisWorkbook.Path & "\"
Pablo Vilas
  • 441
  • 5
  • 11
  • Your way would be good if you could add the &"\" at the very end. myPath = ThisWorkbook.Path & "\" – MKR Jan 31 '21 at 10:37