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

User · Accepted Answer

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

User · Answer

Simple Example below  Sub openPath   Dim path As String path   Application ActivePresentation path Shell Environ  quot windir quot    amp   quot  explorer exe  quot  quot  quot   amp  path  amp   quot  quot   vbNormalFocus End Sub

User · Answer

This is the VBA that I use to open the current path in an Explorer window   Shell Environ  windir    amp    explorer exe      amp  CurDir    amp     vbNormalFocus     Microsoft Documentation     CurDir Function   Environ Function   Shell Function

User · Answer

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  gt  0 Then         ParentPath   ParentPath  amp  Part  amp          End If     Count   Count   1     If Count   Parts Then Exit For Next  MsgBox  File-Drive      amp  ThisWorkbookPathParts           LBound ThisWorkbookPathParts   MsgBox  Parent-Path      amp  ParentPath   But if don t  this should be enough   Solution B   Dim ThisWorkbookPath As String  ThisWorkbookPath   ThisWorkbook Path MsgBox  Working-Directory      amp  ThisWorkbookPath

User · Answer

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

User · Answer

It would seem likely that the ActiveWorkbook has not been saved     Try CurDir   instead

User · Answer

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

User · Answer

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  gt  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

User · Answer

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

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

Examples related to excel

Examples related to vba