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:
MkDir(Path as String)
Path : This is the full path of folder which has to be created.
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.
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