Dynamic Folder Creation in VBA Excel

Question

I have several files that need to be distributed into respective folders. How can I check to see if a folder exists with a dynamic name using an excel VBA macro?

I split a single workbook into several by the various worksheets within it. I believe it would be easiest if the macro used the name of each sheet in the original workbook to check for the existence of that folder. That way it's dynamic and I don't have to worry about coding it to search for each folder, as the data source continues to grow and need additional worksheets. I already have a code for searching for the folder, I just need to understand how to write it so that its dynamic.

Dim Path As String
Dim Folder As String
Dim Answer As VbMsgBoxResult
Dim NewPath As String
NewPath = ActvieWorkbook.Sheets.Name
Path = "C:\Test" & NewPath
Folder = Dir(Path, vbDirectory)
For Each sheetz0r In ActiveWorkbook.Sheets
If Folder = vbNullString Then
    Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
    Select Case Answer
        Case vbYes
            VBA.FileSystem.MkDir (Path)
        Case Else
            Exit Sub
    End Select
End If
Next

In the code I have written, I just need the "NewPath =" line adjusted so that it will search for the sheet names.

Tools > References, then locate and add the Microsoft Scripting Runtime library. Your life will be much, much easier with a Scripting.FileSystemObject. — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2019 at 19:01
That said, NewPath looks like it needs to be re-assigned in the For Each sheetz0r loop body? NewPath = sheetz0r.Name? — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2019 at 19:12
I tried the NewPath = sheetz0r.Name In a previous iteration however I receive an error code that an object is required. — Tony Jeffs
– Tony Jeffs, Commented May 15, 2019 at 20:21
"Object Required" on that value assignment would have been thrown if you did Set NewPath = sheetz0r.Name - only objects can be assigned with the Set keyword — Mathieu Guindon
– Mathieu Guindon, Commented May 15, 2019 at 20:36

Mathieu Guindon · Accepted Answer · 2019-05-15 19:26:07Z

1

Move the Path and Folder assignments inside the loop body, and replace & NewPath with & sheetz0r.Name - not sure what ActiveSheet.Sheets.Name is supposed to be, the Sheets collection class doesn't have a Name member.

I'd restructure things a bit, remove the redundant variables, and move declarations closer to their usage. I think what you mean to do is something like this?

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets

    Dim Path As String
    Path = Dir("C:\Test" & sheet.Name, vbDirectory)

    If Path = vbNullString Then
        If MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?") = vbYes Then
            VBA.FileSystem.MkDir Path
        Else
            Exit For
        End If
    End If
Next

That said, verifying whether a folder exists, and creating a new one, is much simpler/cleaner using a FileSystemObject from the Scripting library - I would also abstract away the prompting part into its own function:

With New Scripting.FileSystemObject
    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Worksheets

        Dim Path As String
        Path = "C:\Test\" & sheet.Name

        If Not .FolderExists(Path) Then
            If ConfirmCreateFolder(Path) Then
                .CreateFolder Path
            Else
                Exit For
            End If
        End If

    Next
End With

Private Function ConfirmCreateFolder(ByVal Path As String) As Boolean
    Dim prompt As String
    prompt = "Folder '" & Path & "' does not exist. Would you like to create it?"
    ConfirmCreateFolder = (MsgBox(prompt, vbYesNo, "Create Folder?") = vbYes)            
End Function

answered May 15, 2019 at 19:26

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Jeffs Over a year ago

Thank you. Sorry my code was so strange with the ActiveSheet.Sheets.Name I meant to take that out before posting. This helps a lot.

Tony Jeffs Over a year ago

My Issue has been solved. Sadly I do not have enough credits to upvote the answer.

Mathieu Guindon Over a year ago

@TonyJeffs not until you get 15 points. But you can still mark an answer as "accepted" by clicking the hollow checkmark next to the voting buttons. That'll give you +2 already =)

Jitendra Singh · Accepted Answer · 2019-05-15 19:26:17Z

0

Try this

Sub CheckFolder()
    Dim Path As String
    Dim Folder As String
    Dim Answer As VbMsgBoxResult
    Dim NewPath As String
    Dim scripObj As New Scripting.FileSystemObject


    Path = "C:\Test\"
    For Each sheetz0r In ActiveWorkbook.Sheets

    If Not scripObj.FolderExists(Path & sheetz0r.Name) Then
        Answer = MsgBox("Path does not exist. Would you like to create it?", vbYesNo, "Create Path?")
        Select Case Answer
            Case vbYes
                scripObj.CreateFolder (Path & sheetz0r.Name)
            Case Else
                Exit Sub
        End Select
    End If
    Next
End Sub

answered May 15, 2019 at 19:26

Jitendra Singh

1911 gold badge1 silver badge9 bronze badges

1 Comment

Tony Jeffs Over a year ago

Thank you for your insight!

Collectives™ on Stack Overflow

Dynamic Folder Creation in VBA Excel

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related