How to create folders in windows via Excel VBA

.

Dear LEM Readers,
Till now, I had published many articles on how to list files from a folder / sub folder, how to list folders and sub folders etc. You get a FREE excel workbook as well to play around.
In both above articles, You have learnt playing around an existing folders or files in folders. Then I thought of publishing an article where I can teach you how to CREATE a folder in windows using excel programming. There is a very simple VBA function which enables you to create a folder in windows:

Create folder - VBA code

Create folder – VBA code

MkDir(Path as String)

Where:
Path : This is the full path of folder which has to be created.

Example: MkDir(“C:\Vishwa\MyFolders\Folder1”)

In the above example, MkDir will first look for this Directory – C:\Vishwa\MyFolders and then create a folder named “Folder1” inside that.
Note: If root directory i.e. C:\Vishwa\MyFolders not found then, folder will not be created and this VBA function will throw an error (Path Not Found)

How to create a Folder in Windows using VBA

As explained above, I have created a function which will create a folder inside a root directory specified.


Note:

As you can see that it might be possible that you may give a path name which is already existing and then it will lead to an error. So before creating a folder, how do we make sure if this path already exists or not?

How to check if a directory is already existing ?

For the above CreateFolder Function to create a folder successfully there are two conditions which should be met:
1 . rootDirectory = “C:\Vishwa\MyFolders\” should be existing in windows.
2 . folderToBeCreated = “MyFolder1” should not be existing inside the rootDirectory already.

To check this you can use another VBA function called Dir(pathName, vbDirectory) as String

What will below statement do?

Dir(rootDirectory , vbDirectory)

In the above example, rootDirectory = “C:\Vishwa\MyFolders\”.
This function will return the name of the child folder i.e. MyFolders in the above directory full path if and only if this is an existing path in windows.

Therefore by below code you can make sure that rootFolderPath is existing and FolderName does not exists in the root folder before trying to create a new folder inside that.

    ' Check the root directory and folder path
    ' before creating it directly
        If Len(Dir(rootDirectory, vbDirectory)) <> 0 Then 'check if RootDirectory Exists?
            If Len(Dir(path, vbDirectory)) = 0 Then ' full path should not exist already
                VBA.MkDir (path) 
                MsgBox "Folder is created successfully"
            Else
                MsgBox "Folder is already existing in the root directory"
            End If
        Else
            MsgBox "Root directory does not exist"
        End If

Therefore your complete code for creating a folder inside a directory will look like below which will give you correct error message:


    Sub CreateFolder()
    Dim rootDirectory As String
    Dim folderToBeCreated As String
    Dim path As String
    ' Set the root directory path
    ' where you want to create
    ' your folder
        rootDirectory = "C:\Vishwa\MyFolders"
    ' give a valid name for your folder
        folderToBeCreated = "MyFolder1"
    ' Path for MkDir VBA function
    ' would be the concatination
    ' of above two
        path = rootDirectory & folderToBeCreated
    ' Check the root directory and folder path
    ' before creating it directly
        If Len(Dir(rootDirectory, vbDirectory)) <> 0 Then 'check if RootDirectory Exists?
            If Len(Dir(path, vbDirectory)) = 0 Then ' full path should not exist already
                VBA.MkDir (path) ' or VBA.MkDir ("C:\Vishwa\MyFolders\MyFolder1")
                MsgBox "Folder is created successfully"
            Else
                MsgBox "Folder is already existing in the root directory"
            End If
        Else
            MsgBox "Root directory does not exist"
        End If
End Sub

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Szymek

    Hi
    THANKS A LOT for Excel foder structure

    Reply

Trackbacks/Pingbacks

  1. Welcome to LearnExcelMacro.com Folder Structure Creator - A FREE Excel VBA Tool - […] readers, In my previous article I posted an article about how to create folder in windows by Excel VBA…
  2. Folder Structure Creator - A FREE Excel VBA Tool - […] readers, In my previous article I posted an article about how to create folder in windows by Excel VBA…
  3. Excel VBA Code to List files from Folder and Sub-folders in Excel Workbook - […] all Folders and Sub-folders in Hierarchical Structure [FREE DOWNLOAD] How to create folders in windows via Excel VBA Download…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest