Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
    End Sub
share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .