Current Directory (CurDir) and Change Directory (Chdir) in VBA Excel

Objective

To find the current directory using CurDir function and change the directory using ChDir statement.

Approach

In this VBA Excel automation, we have checked CurDir function and ChDir statement by simply writing them in a macro file, and we have monitored the results in Local window.

Details

CurDir Function

It is a function which provides us the “current directory” in which a user exists. This function can be used when we are manipulating or handling files, creating folders, moving between folders etc. If we look at the image below, we can see the current directory is displayed in the message box.

 

Code ( CurDir )

Sub CurrentDirectory()
Dim MyCurrentDir As String

'Show current directory
MyCurrentDir = CurDir
MsgBox "Current Directory is " & MyCurrentDir

End Sub

 

When we execute the above code, a message box will be displayed which will showcase the current directory.We can see the same in the image below.

 

vba current directory curdir


ChDir Statement

This function changes the current directory to another directory path which is mentioned by us. It does not change the path of the current directory permanently, rather it changes current directory until the macro file is opened. Once we close the macro file and reopen it, default current directory is reset to its system defined path.

In the code below we had created 2 variable “CurrentDirBefore” and “CurrentDirChanged”. We have used the chdir statement in the code to change current directory.

Code (ChDir)

Sub ChangeDir()
Dim CurrentDirBefore, CurrentDirChanged

'Show current directory
CurrentDirBefore = CurDir

'Change Current directory
ChDir "C:\Users\yourusername\Desktop\macro\Final\New\"

CurrentDirChanged = CurDir

End Sub

 

When we execute the code mentioned just above,current directory will be changed by ChDir statement. In the screenshot below we can see that earlier current directory was “C:\Users\YourUserName\Documents” , now the changed current directory is C:\Users\YourUserName\Desktop\macro\Final\New\.

 

vba chdir change directory

 

 

Move One Level Up

To move one level up in the current directory we use double dot sign (..). Using this with chdir statement will take us one level up in the current directory. We have used ChDir “..” statement in the code below.We can see in the image below that current directory has been changed to one level up.

 

Code [Move One Level Up ]

Sub OneLevelUpDir()
Dim CurrentDirBefore, CurrentDirChanged

'Show current directory
CurrentDirBefore = CurDir

'Move one level up in directory
ChDir ".."

CurrentDirChanged = CurDir

End Sub

 

In the screenshot below we can see that earlier the current directory was “C:\users\YourUserName\Documents” , now the current directory is one level up , i.e. C:\users\YourUserName\

 

 

vba chdir change directory 

 

Move to root level

To move to root level from current directory we use back slash sign (\). Using this with chdir will take us to root of the current directory. Here we use ChDir “\” , it will change the current directory to root directory of that drive. We can see the same in the image below.

In the below screenshot we can see that current directory has changed, now the current directory is the root(C:\) of the drive. 

 

vba chdir change directory

 

Code [Move to root directory]

Sub RootLevelDIr()
Dim CurrentDirBefore, CurrentDirChanged

'Show current directory
CurrentDirBefore = CurDir

'Move to root of directory
ChDir "\"

CurrentDirChanged = CurDir

End Sub

 

Notes

  • We can use chdir statement when we navigate to a folder to read/write a file
  • CurDir function can be used to obtain the folder path while we save a file 


Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/curdir-function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/chdir-statement

Post you may like

VBA Excel Array Concepts