creating a list of folders in excel

Question

I'm trying to create a list of folders, based off row data in Excel. Obviously I've copied this common code from elsewhere.

It worked the first time I used it, but when I went to create another run of folders, it spit out this error: "run time error 76: path not found"

I've saved the file into its own new folder, so the only thing that exists in the folder is the excel workbook.

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then _
     MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))

Thanks

Can you create a minimal reproducible example? What is Rng(r, c)? — BigBen
– BigBen, Commented Apr 13, 2022 at 15:34
You are probably missing the backslash between the workbook path an the content of your cell. Write the name of the full directory into an intermediate variable that you can check with the debugger. — FunThomas
– FunThomas, Commented Apr 13, 2022 at 15:37
bigben, my code is: Sub MakeFolders() Dim Rng As Range Dim maxRows, maxCols, r, c As Integer Set Rng = Selection maxRows = Rng.Rows.Count maxCols = Rng.Columns.Count For c = 1 To maxCols r = 1 Do While r <= maxRows If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then MkDir (ActiveWorkbook.Path & "\" & Rng(r, c)) On Error Resume Next End If r = r + 1 Loop Next c End Sub — shoey
– shoey, Commented Apr 13, 2022 at 15:49
Edit your post and add code there. Use the {} button to format it. — Tim Williams
– Tim Williams, Commented Apr 13, 2022 at 16:23

Gove · Accepted Answer · 2022-04-14 13:18:13Z

I've tried your code and it creates the folders as expected.

Here's what I executed:

Sub MakeFolders()
    Dim Rng As Range
    Dim maxRows As Integer, maxCols As Integer, r As Integer, c As Integer
    
    Set Rng = Selection
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    
    For c = 1 To maxCols
        r = 1
        Do While r <= maxRows
            If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
                MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
                On Error Resume Next
            End If
            r = r + 1
        Loop
    Next c
End Sub

Note that I adjusted your second Dim statement to include the data type for each variable as that seemed your intent. Without including "as Integer" for each variable, all but the last variable in the list was being declared as "variant" instead of "integer".

You definitely want to remove the "on error resume next" statement. Once that line executes, it is suppressing any error message that you would otherwise see to help you find the issue.

My best guess is that you have illegal characters in the data you are using to create the folders. Be sure that none of your folder names include the following characters as they are all disallowed in folder names

< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
\ (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)

I've provided a simplified version of your code in case it is of use.

Sub MakeFolders2()
    Dim cell As Range
    For Each cell In Selection
        If Len(Dir(ActiveWorkbook.Path & "\" & cell.value, vbDirectory)) = 0 Then
            MkDir (ActiveWorkbook.Path & "\" & cell.value)
        End If
    Next
End Sub

Collectives™ on Stack Overflow

creating a list of folders in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related