Excel VBA - Folder Operation - Exists, Create, Copy, Delete Directory

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:

Command	Description	Syntax
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 ] ) or VBA.FileSystem.Dir
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*)
Glossary
*	vbFileAttribute	vbNormal(0) vbReadOnly(1) vbHidden(2) VbSystem(4) vbVolume(8) vbDirectory(16) vbArchive(32) vbAlias(64)

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")'

Command	Description	Syntax
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)
CopyFile	Copy File	CopyFile(Source,Destination)
MoveFile	Move File	MoveFile(Source,Destination)
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.