Here we have a few examples where we can use files and folders in a VBA code.
1. Obtain the active folder path
Enter this line of code in your code if you want to obtain the active folder path where you have saved the current file –
Msgbox “Active folder name: “ &CurDir
A message box will give out this result –
2. Change the drive
If you want to change the drive through a macro, you can do it by using this code line –
ChDrive “D”
This will change the current drive to D so that you can perform further actions like saving files or opening files, etc. Ensure that this drive does exist in the computer system. If not, this line of code will give you an error.
3. Change the active folder
If you want to change the active folder, use this line of code –
ChDir “D:\My Documents\Files”
This will change the current / active folder to the above mentioned path. However, if this path does not exist, this line of code will give you an error. So ensure that the path exists before using this line of code.
4. Check if file exists in a folder
In the vba code, if you want to check if the file exists in the folder, before you proceed with other actions in the code, you can use the following line of code –
If Dir(“D:\My Documents\File1.xlsx”) <> “” then
‘rest of the code
If the file “File1.xlsx” exists at the path given (D:\My Documents\File1.xlsx), then the code proceeds to execute the statements under the IF loop. If the path does not exist, then the code will execute the statements under the Else part of the loop, if it has been mentioned.
5. Delete a File
If you want to delete a file through code, then you have to use a simple line of code in your vba code. The code line is –
Kill “D:\My Documents\File1.xlsx”
The file named File1.xlsx will be deleted. If you do not specify the folder or drive, excel will use the active folder and drive.
6. Create a new folder
If you want to create a new folder in the macro to maybe store a file or files, then you can use either of these code lines –
MkDir “NewFolder” – This will create a new folder in the active folder.
MkDir “D:\My Documents\NewFolder” – This will create a new folder in the existing path given – D:\My Documents\
7. Delete a Folder
You can delete a folder through a macro also. However, you need to ensure that the folder is empty prior to deletion. You can use either of these code lines –
RmDir “NewFolder” – This will delete the subfolder “NewFolder” from the active folder.
RmDir “D:\My Documents\NewFolder – This will delete the subfolder “NewFolder” from the path specified – D:\My Documents\
8. Copy a File
You can make one or more copies of a file using a macro. You can make a copy of the file in the same folder (active folder) or in another folder. However, you need to ensure that the file being copied is closed at the time of making a copy.
FileCopy “File1.xlsx”, “File2.xlsx” – This makes a copy of the file named File1.xlsx and saves it as File2.xlsx in the active folder.
FileCopy “File1.xlsx”, “D:\My Documents\Files\File2.xlsx” – This makes a copy of the file named File1.xlsx in the active folder and saves it as File2.xlsx in the path provided – D:\My Documents\Files\.
9. Move a File
If you cannot delete file with security constraints, however, you can move a file from one location to another, you can do this using a simple line of code. You can move the file from the source to the destination only if the file is closed.
This is the code which you can use –
OldFilePath = “D:\OldFolder\File1.xlsx” ‘source location
NewFilePath = “D:\NewFolder\File1.xlsx” ‘destination location
Name OldFilePath as NewFilePath ‘file has been moved.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
