Excel VBA Chdir
“ChDir” in can be termed as “Change Directory”. By using “ChDir” we can change the current default directory used in VBA when searching for the files without a fully qualified path. For an example when we try to save the file as a new file by default we could see system configured drive opens up and from there on we select the folder we wish to save.
Have you ever thought that we can change that default folder picker window to our wish? Yes, we can do this!
Now let’s look at the syntax of the ChDir function in VBA.
Path: This is where we need to mention the folder path we wish to open by default when we try to open or save as the file to a different name.
The path should be mentioned in double-quotes.
Examples of ChDir Function in VBA
Below are the examples of Excel VBA ChDir.
Now take a look at the below VBA code first.
Sub ChDir_Example1() Dim FD As FileDialog Dim ND As String Set FD = Application.FileDialog(msoFileDialogFilePicker) With FD .Title = "Choose Your File" .AllowMultiSelect = False .Show End With End Sub
When I run this code using F5 key or manually then, it will open up the below default window first.
From here on I have to go through various folders and Sub Folders to select the files I wish to open using VBA code.
This will take a lot of time! If I have to go through various folders then it may also lead to wrong file selection due to various Sub Folders and it adds more confusion.
What if my code can open the specific folder where my file which is required to be chosen??
Isn’t it a cool option to have?
Yes, let’s try this out.
Before passing on our code we need to use the function ChDir and mention the folder path in double quotes.
ChDir "D:\Articles\Excel Files"
The above code by default will change the directory to be opened to “D” drive and under D drive “Articles” folder and under this folder Sub Folder to be opened is “Excel Files”.
Sub ChDir_Example2() Dim Filename As Variant ChDir "D:\Articles\Excel Files" Filename = Application.GetSaveAsFilename() If TypeName(Filename) <> “Boolean” Then MsgBox Filename End If End Sub
Now I will run the code manually or by pressing F5 key and see what file directory opens up.
As we can see in the above picture I got the default window as per my specification.
Just in case if your ChDir function is not showing the mentioned file directory then you need to first change the Drive then apply ChDir function.
To change the drive we need to use “ChDrive” function. Since I want to open the file in “D” drive first I need to change the drive to “D”.
The above code will change the drive to “E”.
Sub ChDir_Example2() Dim Filename As Variant ChDrive "D" ChDir "D:\Articles\Excel Files" Filename = Application.GetSaveAsFilename() If TypeName(Filename) <> “Boolean” Then MsgBox Filename End If End Sub
Like this by using VBA “ChDir” function, we can change the default file directory to our wish.
This has been a guide to VBA ChDir. Here we learn how to use Excel VBA ChDir function to change the directory or drive along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –
- VBA Variant
- What is CHR VBA Function?
- VBA Block Comment
- VBA Intersect
- FileSystemObject (FSO) in VBA
- Global Variables in VBA
- Activate Sheet in VBA
- FileDialog in VBA
- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion
- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications