CODEWITHSUNDEEP
excelvba
Alex Gordon
Alex Gordon

Reputation: 60871

How to get the path of current worksheet in VBA?

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

Upvotes: 185

Views: 984068

Answers (7)

Mr_robot_mn
Mr_robot_mn

Reputation: 1

Easiest way to find location of workbook

?thisworkbook.path

Into the Immediate Window.

Screenshot of Immediate Window

Upvotes: -1

Chepaki
Chepaki

Reputation: 56

The most exact way to get the worksheet path, assuming you have: ws As Worksheet => ws.Parent.Path

Upvotes: 0

Julio Garcia
Julio Garcia

Reputation: 1

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`

Upvotes: 0

avalanche1
avalanche1

Reputation: 3592

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

Upvotes: 38

Pablo Vilas
Pablo Vilas

Reputation: 586

The quickest way

path = ThisWorkbook.Path & "\"

Upvotes: 4

Alex22
Alex22

Reputation: 529

Always nice to have: 

Dim myPath As String     
Dim folderPath As String 

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

Upvotes: 42

BradC
BradC

Reputation: 39986

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

Upvotes: 349

Related Questions