EXCEL VBA - create directories and folders if they are not already there

Question

I have a list of folder locations in column A that define the directory hierarchy I am trying to create (e.g. "C:\topFolder\nextFolder\lastFolder\). The code runs with no errors, but no folders are created. What am I doing wrong here?

Sub newDestination()
    Dim Path As Variant
    Dim folderLevel As Variant

    For Each Path In Sheet11.Range("A:A")
        For Each folderLevel In Split(Path.Value, "\")
            folderLevel = folderLevel & "\"
            If Len(Dir(folderLevel, vbDirectory)) = 0 Then
                MkDir folderLevel
            End If
        Next folderLevel
    Next Path

End Sub

can you put debug.print(folderLevel) after folderLevel = folderLevel & "\" to see what that value is? — pokemon_Man
– pokemon_Man, Commented Feb 8, 2017 at 15:11
Path is an Excel VBA built-in method, did you not get any error for the Path variable declaration? — Barney
– Barney, Commented Feb 8, 2017 at 15:20
@Barney Path is built in property for few objects but not a built in method in Excel-VBA. Nonetheless not a very good choice as variable name. — cyboashu
– cyboashu, Commented Feb 8, 2017 at 15:34

cyboashu · Accepted Answer · 2017-02-08 15:44:37Z

You are creating orphan folders. mkdir "abc\" will create a folder named abc in the WorkBook's directory. If the workbook is not saved then this folder gets created in MyDocuments (most likey, depending on Excel's config).

You need to pass fully qalified path to create a folder at your desired location like "C:\Temp\abc\"

In your code the issue is with how you are running your for loop and passing the folder name.

This is how you need to do this:

Sub test()

    Dim strPath  As String
    Dim lCtr     As Long

    strPath = "C:\Temp\Parent\Child\ChildsChild"

    arrpath = Split(strPath, "\")
    strPath = arrpath(LBound(arrpath)) & "\"

    For lCtr = LBound(arrpath) + 1 To UBound(arrpath)
        strPath = strPath & arrpath(lCtr) & "\"            
        If Dir(strPath, vbDirectory) = "" Then
            MkDir strPath
        End If
    Next

End Sub

Collectives™ on Stack Overflow

EXCEL VBA - create directories and folders if they are not already there

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related