CODEWITHSUNDEEP
excelvbaloops
Geoff
Geoff

Reputation: 109

Loop through variable to create folders

I tried using the code below to loop through the strDir variable to create 4 different folders in 4 different locations.

It does not create the folders. No errors appear.

Dim i as Integer  
JobName = NewJob.Value

If New_Job.JobYes.Value Then
strDir1 = "C:\QTR\" & JobName & " QTR"
strDir2 = "C:\QT\" & JobName & " QT"
strDir3 = "C:\EMAILS\" & JobName & " EMAILS"
strDir4 = "C:\DOCUMENTS\" & JobName & " DOCS"        

    For i = 1 To 4
        If Dir(strDir, vbDirectory) = "" Then
            MkDir strDir & i
        Else
        MsgBox "Directory exists."
    End If
    Next i
Else
End If

Upvotes: 0

Views: 149

Answers (3)

user4039065
user4039065

Reputation:

I agree with the array approach but avoid creating blank entries in the array. It has a zero-based index (by default) and strDir(4) actually creates 5 entries; e.g. 0, 1, 2, 3, 4.

First off, either put Option Explicit at the top of the code sheet or go into the VBE's Tools ► Options ► Editor and put a check beside Require Variable Declaration. This will quickly identify the use of undeclared variables like the strDir in your code.

Dim d As Long, strDir As Variant, JobName As String

strDir = Array("C:\QTR\" & JobName & " QTR", _
               "C:\QT\" & JobName & " QT", _
               "C:\EMAILS\" & JobName & " EMAILS", _
               "C:\DOCUMENTS\" & JobName & " DOCS")

For d = LBound(strDir) To UBound(strDir)
    If Dir(strDir(d), vbDirectory) = "" Then
        MkDir strDir(d)
    Else
        Debug.Print strDir(d) & " exists."
    End If
Next d

The LBound and UBound functions return the Upper and Lower Boundaries of the array.

Upvotes: 2

user1983507
user1983507

Reputation:

That will indeed give an error, since its not possible to concatenate the "strDir" & i together, to use that specific parameter. Easiest way to solve this correctly is to skip the loop and use:

If Dir(strDir, vbDirectory) = "" Then
    MkDir strDir1
    MkDir strDir2
    MkDir strDir3
    MkDir strDir4
Else
    MsgBox "Directory exists."
End If

If you really need to create an enormous amount of directories, lets say > 10, then you might want to use dynamically requesting parameters by name, but if you don't need it, I would not recommend it.

Upvotes: 0

Fadi
Fadi

Reputation: 3322

Try this code:

Dim i as Integer  
Dim strDir(4) as String
JobName = NewJob.Value

If New_Job.JobYes.Value Then
strDir(1) = "C:\QTR\" & JobName & " QTR"
strDir(2) = "C:\QT\" & JobName & " QT"
strDir(3) = "C:\EMAILS\" & JobName & " EMAILS"
strDir(4) = "C:\DOCUMENTS\" & JobName & " DOCS"        

    For i = 1 To 4
        If Dir(strDir(i), vbDirectory) = "" Then
            MkDir strDir(i)
        Else
        MsgBox "Directory exists."
    End If
    Next i
Else
End If

Upvotes: 1

Related Questions