Btukfyl

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.

I've tested this:

When I open an Excel document D:dbtmptest1.xlsm:

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




• ActiveWorkbook.Path returns D:dbtmp

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.

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

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

Try CurDir() instead.

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.

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

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