How to get current working directory using vba?

Question

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.

Answer 1

When one opens an Excel document D:\db\tmp\test1.xlsm:

• CurDir() returns C:\Users\[username]\Documents

• ActiveWorkbook.Path returns D:\db\tmp

So CurDir() has a system default and can be changed.

ActiveWorkbook.Path does not change for the same saved Workbook.

For example, CurDir() changes when you do "File/Save As" command, and select a random directory in the File/Directory selection dialog. Then click on Cancel to skip saving. But CurDir() has already changed to the last selected directory.

---

[ADD] Resume VBA for different applications

Access D:\db\tmp\test1.accdb, like duckboy81 commented:

1. CurDir() => C:\Users\[username]\Documents
2. Application.CurrentProject.Path => D:\db\tmp

Excel D:\db\tmp\test1.xlsm:

1. CurDir() => C:\Users\[username]\Documents
2. ActiveWorkbook.Path => D:\db\tmp
3. Application.DefaultFilePath => C:\Users\[username]\Documents

Outlook:

1. CurDir() => C:\WINDOWS\System32
2. Application.Session.Stores(1).Filepath => D:\programdata\Outlook\myOutlookDocX.pst

PowerPoint D:\db\tmp\test1.ppt:

1. CurDir() => C:\Users\[username]\Documents
2. ActivePresentation.Path => D:\db\tmp

Word D:\db\tmp\test1.docx:

1. CurDir() => C:\Users\[username]\Documents
2. Application.ActiveDocument.Path => D:\db\tmp
3. Application.ActiveDocument.FullName => D:\db\tmp\test1.docx
4. Application.StartupPath => C:\users\[username]\appdata\roaming\microsoft\word\startup

Answer 2

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

Answer 3

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

Try CurDir() instead.

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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 

Answer 8

Simple Example below:

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

Answer 9

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