Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to generate folders containing certain Excel field entries:

enter image description here

Additionally, I need to append a date to the folder name based on that entry

So If I have the above Excel table, I'd need the following set of folders to be generated in the same directory:

enter image description here

Is this possible using a macro? Also, in case I add another entry how would I generate another folder only for that entry; for example I add another project CWO-1106:

enter image description here

Basically, I'd like to keep updating the Excel sheet and create folders only for the new entries

Any help would be appreciated. Thanks!

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Here is the macro.

  • Make sure the spreadsheet with the Project ID and Start dates is in view (selected) before you run the macro.
  • Set the folder where you want to create these folders in ParentFolderPath

    Sub CreateFolders()
    
    'Variable definations
    Dim FolderListRange As Range
    Dim FolderRange As Variant
    Dim FolderName As String
    Dim ParentFolderPath As String
    
    On Error GoTo Handle
        ' Set the Folder where the individual folders should be created
        ParentFolderPath = "Folders"
    
        Set FolderListRange = ActiveSheet.Range("A2:A64000").SpecialCells(xlCellTypeConstants)
    
        For Each FolderRange In FolderListRange
            If FolderRange.Offset(0, 1).Value = "" Then GoTo Continue
    
            FolderName = ParentFolderPath & "\" & FolderRange.Value & "-" & Format(FolderRange.Offset(0, 1).Value, "dd-mm-yyyy")
    
            If FileSystem.Dir(FolderName, vbDirectory) = vbNullString Then
                FileSystem.MkDir FolderName
            End If
    
    Continue:
        Next
    
    Handle:
    End Sub
    
share|improve this answer
1  
Nicely done, Lee. Just needs the additional hyphen between the project-ID the date. –  CharlieRB Jul 2 '12 at 15:21
    
Thanks a lot! That's really helpful –  prrao Jul 3 '12 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.