Auto create folders using Excel field entries

Question

I need to generate folders containing certain Excel field entries:

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:

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:

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

Any help would be appreciated. Thanks!

Solution

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
  

Comments

[Jeff]

Is it possible to add subfolders containing the Project-ID? Ex: CWO-1101_QUOTE