Sabrina Ayon
In Microsoft Excel, it’s easy to change the directory or folder by using VBA Functions. There are a total of nine File/Directory Functions. One of the important File/Directory Functions in Excel VBA is the VBA ChDir function. In this article, we will look at how this function works in excel VBA.
Download Practice Workbook
You can download the workbook and practice with them.
VBA ChDir Function: Syntax & Arguments
The excel ChDir function is one of the advanced functions pre-owned by VBA. The ChDir indicates “Change Directory”. There are so many built-in functions in Excel VBA. And, the ChDir Function is also a built-in function categorized under File/Directory Functions.
➧ Syntax
The syntax for the VBA ChDir function is:
ChDir(path)
➧ Arguments
| Argument | Required/Optional | Explanation |
|---|---|---|
| Path | Required | This is a string parameter that exhibits the directory or folder we wish to change. |
➧ Return Value
No value.
➧ Type
VBA Function (File/Directory Function).
➧ Applies to
Excel office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011, Excel 2010, Excel 2007, Excel 2003.
4 Uses of VBA ChDir Function
We know that when we want to save an excel file for the first time, we can select a folder for saving the files. After that, when we want to save other excel files, it saved in the same folder by default. We can change the folder or directory by using the excel VBA ChDir function. Let’s look at some uses of the function below.
Suppose that, we have a dataset in our excel file, which contains a students’ marks. The name of the student is Jhon, his subjects are in column C, the marks of each subject are listed in column D. Now, we calculate the total marks in cell C11 by using the SUM function. So, let’s save this file.
If we want to look at where the file is located, firstly, we need to go to File in the ribbon.
In the Home section, we can now view our file location.
If we wish to view the exact location. Just right-click on the file. And, go to Open File Location.
This will take us to the location of the file. And we can see that our file location is D:\Sabrina Ayon.
Let’s change the directory/ folder of the file.
1. Change the Present Folder Using VBA ChDir Function
To change the current folder of the file using VBA ChDir Function, we have to follow some steps below.
STEPS:
- First, go to the Developer tab from the ribbon.
- Second, click on Visual Basic to open the Visual Basic Editor.
- Another way to open the Visual Basic Editor is simply to press Alt + F11.
- Next, go to Insert and select Module from the drop-down menu.
- Or, right-click on the sheet, then select View Code.
- And, this will open up the visual basic window.
- After that, copy and paste the VBA code below.
VBA Code:
Sub VBA_ChDir_Func_ChangeFolder()
Dim chFolder As String
chFolder = "D:\ExcelDemy\1111"
ChDir chFolder
MsgBox "Current Folder is: " & chFolder, vbInformation, "VBA ChDir Function"
End Sub- Next, press the F5 key or click on the Run Sub button to run the code.
Here, we just named the procedure VBA_ChDir_Func_ChangeFolder. Then, declared the variable as a string, cause we know that the parameter of the function is a string expression. We name the variable chFolder. As we want to save our file to the folder “D:\ExcelDemy\1111”. So we keep the path D:\ExcelDemy\1111 to our variable chFolder. And then, locate the file D:\ExcelDemy\1111 by declaring the ChDir function. Finally, the above code will change the folder to “D:\ExcelDemy\1111”. And, show the current file location in a MsgBox.
Output:
After running the code, a MsgBox will appear and display that the Current Folder is: D:\ExcelDemy\1111.
Read More: How to Use MsgBox Function in Excel VBA (A Complete Guideline)
2. Excel VBA ChDir Function to Replace the Current Directory
We can also change the directory using the VBA ChDir Function in excel. To do this, let’s pay attention to the steps below.
STEPS:
- In the beginning, go to the Developer tab > Visual Basic > Insert > Module.
- Or, right-clicking on the worksheet will open up a window. From there go to the View Code.
- And, this will take you to the Visual Basic Editor field, where we can write VBA Macros.
- On the other hand, pressing Alt + F11 will also open the Visual Basic Editor.
- Likewise, in the previous method, we want to change the directory to “C:\Temp”.
- Now, copy and paste the VBA code below.
VBA Code:
Sub VBA_ChDir_Func_ChangeDirectory()
Dim chDirectory As String
chDirectory = "C:\Temp"
ChDir chDirectory
MsgBox "Current Directory is: " & chDirectory, vbInformation, "VBA ChDir Function"
End Sub- Next, run the code by clicking on the Run Sub button or using the keyboard shortcut F5 key.
The superior code will change the directory to “C:\Temp”. This is as same as the earlier method.
Output:
As a result, a MsgBox will reveal the current directory by giving a massage Current Directory is: C:\Temp.
Read More: How to Use VBA Replace Function in Excel (11 Applications)
Similar Readings:
- How to Use VBA Rnd in Excel (4 Methods)
- Use VBA Int Function in Excel ( 3 Examples)
- How to Use VBA IsEmpty Function (5 Relevant Examples)
- Use VBA Mod Operator (9 Examples)
- How to Use VBA IsNumeric Function (9 Examples)
3. Shift One Directory with VBA ChDir Function
To move up one directory, we can use the beneath statement in our code. The two dots (“..”) in the statement is used to construct a comparative directory.
ChDir ".."4. Switch Folder by Opening Drive Using VBA ChDir Function
Assumes that, again our default file path is D:\Sabrina Ayon likewise the first method. Now, we want to change the path to D:\ExcelDemy\1111 by opening the drive manually. For this proceed the steps down.
STEPS:
- Similarly, first, open the visual basic editor by right-clicking on the sheet from the sheet bar and then go to View Code.
- Next, write down the VBA code here.
VBA Code:
Sub ChDir_Func_Change()
Dim Folder As Variant
ChDir "D:\ExcelDemy\1111"
Folder = Application.GetSaveAsFilename()
If TypeName(Folder) <> “Boolean” Then
MsgBox Folder
End If
End Sub- Finally, Run the code by clicking the Run Sub button, on the other hand, press the keyboard shortcut F5 key to run the code.
In the above code, we name the procedure ChFir_Func_Change. And declare the variable name Folder as Variant. We can save the file manually by typing the statement Application.GetSaveAsFilename. By this, the Save As window will appear and we manually save our file where we wish to save.
- Finally, after running the code, go to the folder D:\ExcelDemy\1111 and click on Save.
Related Content: How to Use VBA Switch Function (6 Suitable Examples)
Things to Remember
- The VBA ChDir Function can only change the folder/directory of an excel file, this function can’t change the drive of a file. To change a drive we use the VBA ChDrive function.
Conclusion
The above examples are an overview of the VBA ChDir function in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!
