Perhaps not excel related but.....maybe

[kevinonearth]

.....could anybody direct me in the right direction:

I want to be able to automate the process of creating folders and sub folders.


Can I do this in VBA or how can I do it


For example if I have a folder called

2013 can I then populate that folder with subfolders for

January February March April....

and populate those subfolders with

Week 1

Week 2

Week 3


and so on?


Can you advise or at least point me in the right direction?

 

[Wookiee]

First off, I love that user name!


Second, yes, you most certainly can create folder with Excel VBA. Here's a bit of code I put together which creates a series of folders and subfolders. You might have to play around with it to get it set up with the structure you like, but I think it's a good starting point.

[pre]

Private Const strRoot As String = "SharedDrivePathRootFolder"

Private Const strM1 As String = "1. First Folder"
Private Const strM2 As String = "2. Second Folder"
Private Const strM3 As String = "3. Third Folder"
Private Const strM4 As String = "4. Fourth Folder"

Sub MakeFolders()

'Declare Variable
Dim strName As String, lngLoop As Long, arrValues As Variant

'Create Array To House Subfolders
arrValues = Array("Group 1", "Group 2", "Group 3")

'Loop To Create Subfolders From Array With Common Structures For Each
For lngLoop = LBound(arrValues) To UBound(arrValues)

strName = strRoot & arrValues(lngLoop) & ""

MkDir strName
MkDir strName & strM1
MkDir strName & strM1 & "2013"
MkDir strName & strM2
MkDir strName & strM3
MkDir strName & strM4

Next lngLoop

End Sub

[/pre]

I would suggest creating a dummy folder on your own PC's hard drive to test the macro. Once you've got it working the way it should, then you can change the value in the strRoot variable to the actual drive location in which you want to create your folders.


Hope this helps!


Wookiee

 

[Wookiee]

It occurred to me you might want to work from a list of folders instead of manually creating an array in your code. Below is an alternate version of my macro. It still uses all the constant variables from the first code, but this module will loop through a list of values in Column A to get the folder names.

[pre]

Sub MakeFolders_II()

'Declare Variable
Dim strName As String, lngLoop As Long

'Loop To Create Subfolders From Array With Common Structures For Each
For lngLoop = 1 To 10  'Change 10 to however many rows contain folder names

strName = strRoot & Range("A" & lngLoop).Value & ""

MkDir strName
MkDir strName & strM1
MkDir strName & strM1 & "2013"
MkDir strName & strM2
MkDir strName & strM3
MkDir strName & strM4

Next lngLoop

End Sub

[/pre]

 

[kevinonearth]

you are a legend, that works.

Thanks

kevin on earth

 

[shrivallabha]

Some time back I had written one Knowledge Base entry on VBAX which might be useful for you.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1122


Download the zip file posted.