• Status: Solved
• Priority: Medium
• Security: Public
• Views: 817

# Using Excel to create multiple Folders with subfolders

Hi all

One of my users has requested that I create a new directory for them, the directory will consist of one parent Folder with mulitple subfolders, these subfolders will then also have subfolders.
The user was kind even to send a list of all folders needed to me on a Excel spreadsheet.

The problem is, this directory consists of over 300 folders, each with its own unique name adn as you can imagine, it will take rather long to create.

Is there a way to do this via a formula?

e.g.

Column A contains the command mkdir, column B contains the first folder to be created and column the second folder and so on. Thus far I have been able to use a "formula" to create the first folder, the formula looks as follows: =A3 & B3 the out put of this was: mkdir Folder 1
Cell A contained the command mkdir and cell B contained the name of the first folder... Folder 1.

How can I expand on this to create all required subfolders and their subfolders?

0
DJMohr
• 12
• 4
1 Solution

Sales ManagementCommented:

``````Function ExtractFilePath(ByVal S As String) As String
Dim i As Long

i = InStrRev(S, "\")
If i = 0 Then
i = InStrRev(S, ":")
End If

ExtractFilePath = Left(S, i)
End Function
''
Function ForceMkDir(ByVal S As String) As Boolean
ForceMkDir = False
If Mid(S, Len(S), 1) = "\" Then
S = Left(S, Len(S) - 1)
End If

If (Len(S) < 3) Or FolderExists(S) Or (ExtractFilePath(S) = S) Then
Exit Function
End If

ForceMkDir ExtractFilePath(S)
MkDir S
ForceMkDir = True
End Function
``````
0

Author Commented:
Ok, how and where would i use this?
0

Sales ManagementCommented:
Create the full path in a cell eg in A5 then use "=ForceMkDir(A5)"

If you post the file it would be easier to help you....
0

Sales ManagementCommented:
by full path I mean "C:\Folder1\Folder2"
0

Sales ManagementCommented:
Push Alt + F11
Add the code into a standard module
0

Sales ManagementCommented:
ooops
I left out 2 functions this needs
Here are all functions
0

Sales ManagementCommented:
Attached
``````Function ForceMkDir(ByVal S As String) As Boolean
ForceMkDir = False
If Mid(S, Len(S), 1) = "\" Then
S = Left(S, Len(S) - 1)
End If

If (Len(S) < 3) Or FolderExists(S) Or (ExtractFilePath(S) = S) Then
Exit Function
End If

ForceMkDir ExtractFilePath(S)
MkDir S
ForceMkDir = True
End Function

Function ExtractFilePath(ByVal S As String) As String
Dim i As Long

i = InStrRev(S, "\")
If i = 0 Then
i = InStrRev(S, ":")
End If

ExtractFilePath = Left(S, i)
End Function
''

Function SF_InstrRev(ByVal Haystack As String, ByVal Needle As String) As Long
'find the last occurence of needle in haystack (the VB instr function finds the first occurence)
'SF_InstrRev("   This is   my string   ","i") = 20
Dim i As Long, J As Long
i = InStr(1, Haystack, Needle, vbBinaryCompare)
If SF_isNothing(Needle) Then
SF_InstrRev = 0
Else
If i = 0 Then
SF_InstrRev = 0
Else
If StrComp(Needle, Haystack, vbBinaryCompare) = 0 Then
SF_InstrRev = 1
Else
For J = Len(Haystack) To 1 Step -1
i = InStr(J, Haystack, Needle, vbBinaryCompare)
If i > 0 Then
SF_InstrRev = i
Exit Function
End If
Next J
End If
End If
End If
End Function

Function SF_isNothing(ByVal Haystack As String) As Boolean
'check if there is anything in a string (to avoid testing for
'isnull, isempty, and zero-length strings)
'SF_isNothing("   This is   my string   ") returns False
If Haystack & "" = "" Then
SF_isNothing = True
Else
SF_isNothing = False
End If
End Function
``````
0

Sales ManagementCommented:
LOL
Ignore that -It doesnt use those 2 extra functions
0

Sales ManagementCommented:
How did you go?
0

Author Commented:
Sorry man, been bit busy.

So just to recap, In cell A5 I create the full UNC path: c:/Folder1/Folder2...
then I do something with the code you provided...
Do I create a batch file and run it?
0

Sales ManagementCommented:
Will set it up
0

Sales ManagementCommented:
No Batch
just set up the sheet and when it calculates it sets up
Or you can create a macro to do it

Post the sheet with the folders
0

Author Commented:
0

Sales ManagementCommented:
Ok
Here tis

I have only set up the first 33 in the electrical one as I dont want to rob you of all that fun
:-)

Be careful as its very powerful and you can make a big messy nest of folders

I would delete any folders made during testing and recreate teh lot once you are happy

Enjoy
Create-Folders.xls
0

Sales ManagementCommented:
nb
I left this file as automatic calculation but you could easily change it to manual calculation to make it more controlled
0

Author Commented:
Thanks man
0

## Featured Post

• 12
• 4
Tackle projects and never again get stuck behind a technical roadblock.