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.
Syntax
MkDir “path”
The Argument is the path which is the folder /directory being created.
Returns
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.
Video:
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
Sheet1.Select
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
Sheet2.Select
Range(“A1”).Select
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
Wend
On Error Resume Next
End If
r = r + 1
Loop
Next c
endmacro:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Recent Comments