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:

1. List Folder or check folder exists?
2. Create/Delete Folder. (Table1)
3. Copy or Move Folder. (Table2)
4. Current working directory
5. Get or Set file or folder attributes
6. Open a folder from Excel
7. 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.

1. Create a New Excel Workbook and Press Alt + F11.
2. Then copy paste the below code and run it by pressing F5.
3. 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:

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”)’

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.