-2

I am a total newbie in VBA so I hope you could help me with this. I want to create a folder inside a given path. Inside the folder, I want a subfolder. The name of the folder will be indicated in column A and of the subfolder in column B of an excel worksheet. The path will be in column C.

enter image description here

I would like to be able to click on a cell on column D which would activate a macro who will then create the folder and subfolder. I was hoping to be able to achieve this by using this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
   If Not Application.Intersect(Target, Range("c2:c10000")) Is Nothing Then Call CreatePath
End Sub

So I need the function CreatePath. When a cell is selected on Column D, the CreatePath function should identify the row of the selected cell from column D, take from that row the corresponding name of the folder, subfolder and the path and create the folder and subfolder. Any idea how this function should look like? Please keep in mind that I have started to "play" two days ago with VBA so my know-how is very limited. Thank you all for your support.

braX
  • 11,506
  • 5
  • 20
  • 33
utzerc
  • 1
  • 1
  • 2
    Maybe try searching for answers first? If you Google "Excel vba create folder" this is one of the first results: https://stackoverflow.com/questions/10803834/create-a-folder-and-sub-folder-in-excel-vba – Beek Feb 11 '20 at 13:21

1 Answers1

0

You could do it like this.

Sub CreateFoldersandSubFolders()

'Execute next line in case of error.
On Error Resume Next

'Loop through all the cells  in column 1 in Active Sheet.
For i = 1 To ActiveSheet.UsedRange.Rows.Count

'Name of the Folder
sFolderPath = Cells(i, 1)  'Replace This.Workbook.Path with any location Example "C:\" & Cells(i,1)

'Creating Folder Using Shell Function
Shell "cmd /c mkdir """ & sFolderPath & """", vbHide 'It will Execute Dos Command and use MKDIR to Make Directory of sFolderPath Value

'To continue the Loop
Next i

'Displaying Message
MsgBox "Folders Created"

End Sub

My setup:

enter image description here

After the code runs:

enter image description here

ASH
  • 20,759
  • 19
  • 87
  • 200
  • Thanks, ASH. Your code is creating all the given paths from column A (your setup). I would like to create only the path given by the intersection of column A with the row I have selected. – utzerc Feb 12 '20 at 10:20
  • You can filter out unwanted rows. Is that what you are referring to? – ASH Feb 12 '20 at 18:55
  • In your excel setup I will consider cells C1:C3 as triggers of your macro. So each time I select one cell from column C, your macro should trigger. This will be achived by using the Private Sub Worksheet_SelectionChange (see my 1st post). Now, when selecting one of the C1:C3 cells, your code creates all the paths from cells A1:A3. What I need is to create only the path found at the intersection between the row of the selected C cell and column A. For exmple, if I select row C2, only path from A2 should be created. If I select C3, only path from A3 and so on. – utzerc Feb 13 '20 at 08:51