How to get the path of current worksheet in VBA?

Question

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)?

Annecy

do you mean that, given a worksheet, you want to know the folder where the parent workbook is saved? – Slayton

Answer 1

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

Coeternal

Activeworkbook depends on which workbook is active. USe Thisworkbook.path – Schistosomiasis

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). – Coeternal

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). – Lundgren

@Lundgren 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. – Coeternal

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. – Menarche

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. – Mosemoseley

Answer 2

Always nice to have:

Dim myPath As String     
Dim folderPath As String 

folderPath = Application.ActiveWorkbook.Path    
myPath = Application.ActiveWorkbook.FullName

Eider

Answer 3

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).

Oodles

@avalanche1.That is totally correct, activeworkbook raised alot of unexpected results with me – Cotenant

Answer 4

The quickest way

path = ThisWorkbook.Path & "\"

Lizarraga

Your way would be good if you could add the &"\" at the very end. myPath = ThisWorkbook.Path & "\" – Frugal

Answer 5

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`

Lorsung

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. – Mosemoseley

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 – Epithalamium