Macro for creating a folder

[JKMyx]

Hi

I have below code for creating a folder in the same directory as the active workbook.
The folder name should be the content of the selected cells when I run the macro.

However, I get an error "Bad file name or number (Error 52)" on this code line when debugging:

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

Any idea, what the issue could be?

Here's the code I use:

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(ThisWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ThisWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c

End Sub

 

[Joe4]

A few things.

This:

Dim maxRows, maxCols, r, c As Integer

only declares c as Integer. The others will be set to Variant.
You need to declare each one explicitly, i.e.

Dim maxRows As Integer, maxCols As Integer, r As Integer, c As Integer

I don't think this is not a valid reference:

Rng(r, c)

Your code can be simplified. No need to find the rows and columns. Simply loop through all the cells in your Selection, i.e.

Sub MakeFolders()

    Dim cell As Range

    On Error Resume Next
   
    For Each cell In Selection
        If Len(Dir(ThisWorkbook.Path & "\" & cell.Value, vbDirectory)) = 0 Then
            MkDir (ThisWorkbook.Path & "\" & cell.Value)
        End If
    Next cell
   
    On Error GoTo 0
   
End Sub

 

[JKMyx]

Hi Joe4,

Thanks for your answer!
I used your simplified code, it works perfectly fine, thanks.

Now I'm confronted with another issue... When I use this macro to make a folder in my One Drive, it doesn't work!
It only works if the folder is created on the local disk.

Do you have any clue to get around this problem with code?

Thanks again for your help

 

[Joe4]

Sorry, I do not have any experience with OneDrive.
I did find this, maybe it will help: https://www.myonlinetraininghub.com...s-disabled-when-opening-workbooks-in-onedrive

If that does not help, since it is a new question (different from your original question), you can post it to a new thread.

 

[JKMyx]

Ok thanks!