Excel Vba MKDIR statement

Excel Vba MKDIR statement

The Microsoft Excel MKDir statement allow for the automatic creation of folders or directories. It is a built in function that is entered via the visual basic editor.



            MkDir “path” 

The Argument is the path which is the folder /directory being created.


The MkDir statement does not return any data, however if in the case that a folder to be created exists it will cause an error. Also if the path is a complex directory structure, the high level directory must exist first before the lower level directory could be created.


Code for Creating Directories and Subdirectories:

Option Explicit
Option Base 1 ‘Make Arrays begin at 1

Sub createfolder1()

Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim Rng As Range
Dim MyPath As String
Dim FolderCount As Integer
Dim StructArray() As String
Dim SubFolderLoop As Integer

Dim maxRows, maxCols, r, c As Integer

Set Rng = Sheet1.Range(“A1:A100”)

‘Parent folder.
MyPath = “K:\OWM”
‘Create the folders from selected cells
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count
FolderCount = Range(Range(“A1”), Range(“A1”).End(xlDown)).Count
For c = 1 To maxCols
r = 1
Do While r <= maxRows
If Len(Dir(MyPath & “\” & Rng(r, c), vbDirectory)) = 0 Then
MkDir (MyPath & “\” & Rng(r, c))
ReDim StructArray(FolderCount)
For SubFolderLoop = 1 To FolderCount
StructArray(SubFolderLoop) = Sheet1.Cells(SubFolderLoop, 1).Value
Next SubFolderLoop


While ActiveCell.Value <> “”

On Error Resume Next

For SubFolderLoop = 1 To FolderCount

MkDir MyPath & “\” & Rng(r, c) & “\” & ActiveCell.Value

Next SubFolderLoop

On Error GoTo 0

ActiveCell.Offset(1, 0).Select

On Error Resume Next
End If
r = r + 1
Next c

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub