How to get current working directory using vba?
I've tested this:
When I open an Excel document D:\db\tmp\test1.xlsm
:
CurDir()
returnsC:\Users\[username]\Documents
ActiveWorkbook.Path
returnsD:\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.
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.