Excel VBA – Create, Delete, Exists, Copy, Move, Open Folder Operation
Be it any operation on a system directory, like any of these:
- List Folder or check folder exists?
- Create/Delete Folder. (Table1)
- Copy or Move Folder. (Table2)
- Current working directory
- Get or Set file or folder attributes
- Open a folder from Excel
- and so on …
Yes. It is all possible from within the Excel vba code. You could also use the Quick reference Table below the code snippets.
And that is not the end. There is more examples as well …
Now, lets get some sample codes.
1. VBA Code – Check if Folder Exists?
There is inbuilt function within VBA class collection itself. Dir function can be used for this purpose.
Private Sub Check_Folder_Exists() 'Get Root Folder Name Root_Path = "D:TestFolder" 'Folder Already Exists or not? 'VBA.Dir Function can be used to find whether a folder already exists or not. Folder_Status = VBA.Dir(Root_Path, vbDirectory) If VBA.Trim(Folder_Status) = "" Then MsgBox "Folder Does Not Exist" Else MsgBox "Folder Already Exists" End If End Sub
If the folder exists already, then the Dir function will return the folder name. If it does not exist then it will return blank or spaces.
2. Excel VBA: Create/Delete Folder
Assume, we need to create a new folder in D: Drive after checking whether any folder with same name exists the path. We can do this with the following steps and code.
- Create a New Excel Workbook and Press Alt + F11.
- Then copy paste the below code and run it by pressing F5.
- A folder with name “TestFolder” will be created in D: Drive.
Note: In the VB Editor, type VBA.FileSystem (Do not copy paste, type this command), and it will display display the available functions for Folder or File operations.
Private Sub Create_Folder() 'Get Root Folder Name Root_Path = "D:TestFolder" 'Folder Already Exists or not? VBA.Dir Function can be used to find whether a folder already exists or not. Folder_Status = VBA.Dir(Root_Path, vbDirectory) ThisWorkbook.Sheets(1).Cells(2, 1) = Folder_Status If VBA.Trim(Folder_Status) = "" Then VBA.MkDir Root_Path 'VBA.RmDir can be used to Delete a Directory or Folder MsgBox Root_Path & ": Folder Created" Else MsgBox "Folder Already Exists" End If End Sub
Comments are provided inside the code to explain about each function used.
3. Open Folder From Excel
Now, we have created the TestFolder. IF we need to open the Folder from the code itself and want to display it in the desktop, use the below code.
Sub Open_Folder() Dim Launch_App As Object Set Launch_App = CreateObject("Shell.Application") 'File_Path = Sheets("Sheet1").Cells(1, 1) or File_Path = "D:\TestFolder" Launch_App.Open (File_Path) End Sub
In this code, an instance for the object “Shell.Application” is created and pass the Folder path for the .Open Function.
To perform advanced functions on Folders or File System in Windows, Google for File System Objects.
4. Copy or Move Folder – Using Excel VBA
This code used the commands from the Table2. Again, do not forget to add reference to Microsoft Scripting Runtime either thru early or late binding.
Sub VBA_Copy_Folder() 'Add Reference to Microsoft Scripting Runtime Dim fso As FileSystemObject Dim sourcePath As String, targetPath As String 'Before Copying Check if Both Folder exists If fso.FolderExists(sourcePath) And fso.FolderExists(targetPath) Then 'Copy Folder fso.CopyFolder sourcePath, targetPath 'Move Folder fso.MoveFolder sourcePath, targetPath End If End Sub
I guess, we have covered a lot more on this topic.
Table1 – VBA Commands for Folder Operations:
|Dir||1. Widely used to check for folder or file existence.
2. Scans the directory path passed as parameter & returns the string that matches the pattern.
|VBA.Dir ( pathname [ ,attributes ] )
|MkDir||Used to Create a New directory or Folder||MkDir (Path)|
|RmDir||Delete a System folder or a directory||RmDir (Path)|
|ChDir||Change Current working directory or Active folder||ChDir (Path)|
|CurDir||Get current working or active windows directory||CurDir|
|GetAttr||Get File or Folder Attributes. Returns the vbFileAttribute*||VBA.GetAttr (“File_Or_Folder_Path”)|
|SetAttr||Change Attributes of a Folder or File.
Hint: Hide a Folder or make it Read-Only
|VBA.SetAttr (“File_Or_Folder_Path”, vbFileAttribute*)|
Except for hide or lock folders, which is in a separate article, most processes are listed here.
Either you create a folder or delete already existing one, copy it to a new location, move, rename, open. All is possible right from within an Excel VBA macro code.
Table2 – VBA with FileSystemObject:
To use the Filesystemobject in VBA code, add reference to “Microsoft Scripting Runtime” Library.
Or create a late-binding object to this library with command ‘set fsp = CreateObject(“scripting.filesystemobject”)’
|CreateFolder||Create new folder||FileSystemObject.CreateFolder (Path)|
|CopyFolder||Copy Folder from One location to another||FileSystemObject.CopyFolder (Source, Destination, [OverWriteFiles Flag])|
|MoveFolder||Move the File to different path||FileSystemObject.MoveFolder (Source, Destination, [OverWriteFiles Flag])|
|DeleteFolder||Delete Folder||FileSystemObject.DeleteFolder (Path)|
|CreateTextFile||Copy File||CreateTextFile (Path)|
|DeleteFile||Delete File from specific path||DeleteFile(Path)|
This is not the complete list for Filesystemobject. There are more function to it that can read file content & do more processing.
We have listed only the required commands specific to the topic.
If any more reference is requires, please leave a comment. We will add good samples in our page.