Solved VBA to create subfolder

[Rania01]

Dear All,

I tried to create a sub folder I got an error "Run time error 9 Subscript out of range please help how to resolved this issue
I use below VBA

Sub Knop1_Klikken()
Dim sv, i As Long
Set sh = ThisWorkbook.Sheets("Data")
sv = Cells(1).CurrentRegion
  For i = 1 To UBound(sv)
    If Dir(sv(i, 1) & "\" & [F3], vbDirectory) = "" Then
        CreateObject("shell.application").Namespace("C:").NewFolder Split(sv(i, 1), "\", 2)(1) & "\" & [F3]
        sh.Range("B" & i).Value = "Folder Created"
     Else
  
      sh.Range("B" & i).Value = "Folder already available"
    End If
  Next i
End Sub

 

[Isaac]

1. Try just using MkDir, probably easier
2. what line error occurs on?

that error is often because a worksheet name is misspelled

 

[moke123]

Your code isn't taking into account that the 1st cells are "Folder " and "Details"

I made this change and it debug.printed correctly, I think.

For i = 2 To UBound(sv)
    If Dir(sv(i, 1) & "\" & [F3], vbDirectory) = "" Then
    Debug.Print Split(sv(i, 1), "\", 2)(1) & "\" & [F3]
        'CreateObject("shell.application").Namespace("C:").NewFolder Split(sv(i, 1), "\", 2)(1) & "\" & [F3]
        sh.Range("B" & i).Value = "Folder Created"
     Else

result

Data\2022\A\2022-13
Data\2022\B\2022-13
Data\2022\C\2022-13
Data\2022\D\2022-13

Make sure the parent folders exist before trying to make a subfolder.

Personally I like FSO for this type stuff. You can easily test for each section of the path and create it if doesn't exist.

 

[arnelgp]

try this.

 

[Isaac]

Yes, mkdir good for newbies or simple one time stuff but Moke is right, FSO is the bomb.

Honestly, I sometimes get confused by Dir's many nuances (which are its power I know, but still it can be confusing), so even when I only have a one-liner to do, I like using If createobject("scripting.filesystemobject").fileexists("path")=false then..

 

[moke123]

FSO is the bomb

I'm a big fan of Scripting.FileSystemObject, as well as Scripting.Dictionary.
Not quite sure what Scripting.Encoder is or if there's even a use for it in access, but it is an option in intellisense.

 

[Isaac]

thanks I'll have to study up on scripting.dictionary