[excel] How to get current working directory using vba?

I am using MS Excel 2010 and trying to get the current directory using the below code,

    path = ActiveWorkbook.Path

But ActiveWorkbook.Path returns blank.

Your code: path = ActiveWorkbook.Path

returns blank because you haven't saved your workbook yet.

To overcome your problem, go back to the Excel sheet, save your sheet, and run your code again.

This time it will not show blank, but will show you the path where it is located (current folder)

I hope that helped.

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

Use these codes and enjoy it.

Public Function GetDirectoryName(ByVal source As String) As String()
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection

Dim source_file() As String
Dim i As Integer        

queue.Add fso.GetFolder(source) 'obviously replace

Do While queue.Count > 0
    Set oFolder = queue(1)
    queue.Remove 1 'dequeue
    '...insert any folder processing code here...
    For Each oSubfolder In oFolder.SubFolders
        queue.Add oSubfolder 'enqueue
    Next oSubfolder
    For Each oFile In oFolder.Files
        '...insert any file processing code here...
        'Debug.Print oFile
        i = i + 1
        ReDim Preserve source_file(i)
        source_file(i) = oFile
    Next oFile
Loop
GetDirectoryName = source_file
End Function

And here you can call function:

Sub test()
Dim s
For Each s In GetDirectoryName("C:\New folder")
Debug.Print s
Next
End Sub

This is the VBA that I use to open the current path in an Explorer window:

Shell Environ("windir") & "\explorer.exe """ & CurDir() & "",vbNormalFocus

Microsoft Documentation:

• CurDir Function
• Environ Function
• Shell Function

It would seem likely that the ActiveWorkbook has not been saved...

Try CurDir() instead.

Simple Example below:

Sub openPath()
Dim path As String
path = Application.ActivePresentation.path
Shell Environ("windir") & "\explorer.exe """ & path & "", vbNormalFocus
End Sub

If you really mean pure working Directory, this should suit for you.

Solution A:

Dim ParentPath As String: ParentPath = "\"
Dim ThisWorkbookPath As String
Dim ThisWorkbookPathParts, Part As Variant
Dim Count, Parts As Long

ThisWorkbookPath = ThisWorkbook.Path
ThisWorkbookPathParts = Split(ThisWorkbookPath, _
                        Application.PathSeparator)

Parts = UBound(ThisWorkbookPathParts)
Count = 0
For Each Part In ThisWorkbookPathParts
    If Count > 0 Then
        ParentPath = ParentPath & Part & "\"
    End If
    Count = Count + 1
    If Count = Parts Then Exit For
Next

MsgBox "File-Drive = " & ThisWorkbookPathParts _
       (LBound(ThisWorkbookPathParts))
MsgBox "Parent-Path = " & ParentPath

But if don't, this should be enough.

Solution B:

Dim ThisWorkbookPath As String

ThisWorkbookPath = ThisWorkbook.Path
MsgBox "Working-Directory = " & ThisWorkbookPath 

You have several options depending on what you're looking for. Workbook.Path returns the path of a saved workbook. Application.Path returns the path to the Excel executable. CurDir returns the current working path, this probably defaults to your My Documents folder or similar.

You can also use the windows scripting shell object's .CurrentDirectory property.

Set wshell = CreateObject("WScript.Shell")
Debug.Print wshell.CurrentDirectory

But that should get the same result as just

Debug.Print CurDir