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

I've tested this:

When I open 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.

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.