0

Your help thus far is much appreciated. My journey into Excel VBA continues.

My next challenge is creating a folder in a specific destination. The data I'm creating it from is column B which gains a couple lines each week. The routine I'm looking to create would ideally run through all rows starting C2 and create a folder for all new if one hasn't already been created previously.

I've been playing with the below code but it requires me to select the cells I want to create folders for manually and also will not create a folder of my concatenated cell name for some reason?

Sub MakeMyFolder()
    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

I should add my concatenated file names take the form off "123 - test"

My question is how can I make the routine work without selection? My data is from C2 to last line of data in Column C.

Your help is always appreciated.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Gary Ziabek
  • 21
  • 2
  • 3
    First recommendation: Never use `On Error Resume Next` - instead, test for the condition which you think could error. – braX Jan 10 '18 at 16:14
  • Makes sense braX. No doubt in this condition with large data and folder creations it will be easy to miss a mistake of some sort. – Gary Ziabek Jan 10 '18 at 16:16
  • Try it without the parenthesis: `MkDir ActiveWorkbook.Path & "\" & Rng(r, c)` (also, your question is a bit unclear) Are you asking about how to make it work without Selection? or asking why it's not creating the directory? – braX Jan 10 '18 at 16:22
  • 2
    Brilliant, that worked, Thankyou. I agree, I'll update my question with edit. – Gary Ziabek Jan 10 '18 at 16:31
  • @GaryZiabek Note that `Dim maxRows, maxCols, r, c As Integer` will only declare `c` as `Integer` the others remain `Variant`. You need to specify a type for **every** variable! Also [always use `Long` instead of `Integer`](https://stackoverflow.com/a/26409520/3219613) especially when dealing with row counts (Excel has more rows than `Integer` can handle): `Dim maxRows As Long, maxCols As Long, r As Long, c As Long` – Pᴇʜ Jan 11 '18 at 06:58

0 Answers0