VBA - creating folders to be automated

[superfb]

Hi All,

I have the following code..

Dim lngCount As Long, Msg As String
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   MkDir Cells(1, 20)
Else
    Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
    Loop
   MkDir Cells(1, 20) & " - " & lngCount
   Msg = " - " & lngCount
End If

' message box
MsgBox "Folder Created for:" & vbCr & vbCr & Format(Cells(2, 12), "YYYYMMDD") & Msg, vbOKOnly, "View History Report"

Is there a way to loop it so it creates the folder in column c and goes up to create next one, so after I select ok on msgbx ......

 

[superfb]

Hi All,

I have the following code..

Dim lngCount As Long, Msg As String
lngCount = 2
If Len(Dir(Cells(1, 20), vbDirectory)) = 0 Then
   MkDir Cells(1, 20)
Else
    Do Until Len(Dir(Cells(1, 20) & " - " & lngCount, vbDirectory)) = 0
        lngCount = lngCount + 1
    Loop
   MkDir Cells(1, 20) & " - " & lngCount
   Msg = " - " & lngCount
End If

' message box
MsgBox "Folder Created for:" & vbCr & vbCr & Format(Cells(2, 12), "YYYYMMDD") & Msg, vbOKOnly, "View History Report"

Is there a way to loop it so it creates the folder in column c and goes up to create next one, so after I select ok on msgbx ......

Sorry i dont think my request was clear....

What i mean is, that the folders created begins for example C10, then to C9, only then a message box should appear telling me that this folder is created, when i press ok then the next folder is created and a message box reappearing.

ive tried different codes but none give me the requires results

 

[Joe4]

In looking at your question and code, you mention column C (3rd column), but your code references column number 20 (which is column "T"). So I am a little confused by that apparent discrepancy.
What row in column C do these folders start in?
Can you post a few examples of what values in these cells look like?

 

[superfb]

In looking at your question and code, you mention column C (3rd column), but your code references column number 20 (which is column "T"). So I am a little confused by that apparent discrepancy.
What row in column C do these folders start in?
Can you post a few examples of what values in these cells look like?

Apologies, that code referes to one particular cell when i type in the data,

Column C has a list of the data for example

[TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD]23/02/2010[/TD]
[/TR]
[TR]
[TD]11/02/2010[/TD]
[/TR]
[TR]
[TD]14/01/2010[/TD]
[/TR]
[TR]
[TD]11/01/2010[/TD]
[/TR]
[TR]
[TD]01/12/2009[/TD]
[/TR]
[TR]
[TD]30/09/2009[/TD]
[/TR]
[TR]
[TD]17/09/2009


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Column C has a list of the data for example

[TABLE="class: cms_table, width: 106"]
<tbody>[TR]
[TD]23/02/2010[/TD]
[/TR]
[TR]
[TD]11/02/2010[/TD]
[/TR]
[TR]
[TD]14/01/2010[/TD]
[/TR]
[TR]
[TD]11/01/2010[/TD]
[/TR]
[TR]
[TD]01/12/2009[/TD]
[/TR]
[TR]
[TD]30/09/2009[/TD]
[/TR]
[TR]
[TD]17/09/2009

[/TD]
[/TR]
</tbody>[/TABLE]

So, from that list, you want to create folders named:
20100223
20100211
20100114
20091201
20090930
20090917
Is that correct?

What drive/path should they be created under?

 

[superfb]

That is correct!

I think the aim here is to creat the folder one by one after pressing ok.

I have a code that can create them in bulk but when I am saving document from online in to these folders it can be misplaced.

I was going to have a formula next to col d.
That combines the filepath and the folder to be created.

So from
D1

D:/my computer/files/folders/20100223

D2
D:/my computer/files/folders/20100211

 

[Joe4]

Are you looking for something like this?

Sub MakeFolders()

    Dim lastRow As Long
    Dim r As Long
    Dim dirName As String
    
'   Find last row with data in column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lastRow
        dirName = Cells(r, "D")
        MkDir dirName
        MsgBox "Folder created with name: " & dirName, vbOKOnly
    Next r

End Sub

 

[superfb]

Are you looking for something like this?

Sub MakeFolders()

    Dim lastRow As Long
    Dim r As Long
    Dim dirName As String
    
'   Find last row with data in column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lastRow
        dirName = Cells(r, "D")
        MkDir dirName
        MsgBox "Folder created with name: " & dirName, vbOKOnly
    Next r

End Sub

would this start from the bottom and upwards?? Could there be a cancel button so i could exit mid way?

also would it be able to create 5 folders in one go, and then the next 5???? Thank you

 

[Joe4]

would this start from the bottom and upwards??

No, this works from the top down. Is there any reason you would need it to work in reverse order?

Could there be a cancel button so i could exit mid way?

also would it be able to create 5 folders in one go, and then the next 5????

Try something like this (untested):

Sub MakeFolders()

    Dim lastRow As Long
    Dim r As Long
    Dim dirName As String
    Dim ct As Long
    Dim fldrs As String
    Dim cont
    
'   Find last row with data in column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lastRow
        dirName = Cells(r, "D")
        MkDir dirName
'       Add folder name to list and increment count
        ct = ct + 1
        fldrs = flders & "Folder created with name: " & dirName & vbCrLf
        If c = 5 Then
'           Return message box after 5 and ask if they want to continue
            cont = MsgBox(fldrs, vbYesNo, "Do You Want to Continue?")
            If cont = vbNo Then
'               If no, exit sub
                Exit Sub
            Else
'               Else reset counts and continue
                ct = 0
                fldrs = ""
            End If
        End If
    Next r

End Sub

 

[superfb]

No, this works from the top down. Is there any reason you would need it to work in reverse order?


Try something like this (untested):

Sub MakeFolders()

    Dim lastRow As Long
    Dim r As Long
    Dim dirName As String
    Dim ct As Long
    Dim fldrs As String
    Dim cont
    
'   Find last row with data in column D
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lastRow
        dirName = Cells(r, "D")
        MkDir dirName
'       Add folder name to list and increment count
        ct = ct + 1
        fldrs = flders & "Folder created with name: " & dirName & vbCrLf
        If c = 5 Then
'           Return message box after 5 and ask if they want to continue
            cont = MsgBox(fldrs, vbYesNo, "Do You Want to Continue?")
            If cont = vbNo Then
'               If no, exit sub
                Exit Sub
            Else
'               Else reset counts and continue
                ct = 0
                fldrs = ""
            End If
        End If
    Next r

End Sub

Is it possible for folders that are added twice or multiple times that theres a -1 added?

eg

20150502
20150502 - 2
20150502 - 3
20150502 - 4
20150502 - 5