To find the current directory using CurDir function and change the directory using ChDir statement.
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.
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.
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.
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\.
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\
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.
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
- 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
Post you may like