?
Solved

Using Excel to create multiple Folders with subfolders

Posted on 2011-03-03
16
Medium Priority
?
791 Views
Last Modified: 2012-05-11
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?

Thanks in advance
0
Comment
Question by:DJMohr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 4
16 Comments
 
LVL 5

Expert Comment

by:sirplus
ID: 35026570

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

Open in new window

0
 
LVL 1

Author Comment

by:DJMohr
ID: 35026580
Ok, how and where would i use this?
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35026601
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 5

Expert Comment

by:sirplus
ID: 35026614
by full path I mean "C:\Folder1\Folder2"
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35026624
Push Alt + F11
Add the code into a standard module
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35026680
ooops
I left out 2 functions this needs
Here are all functions
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35026685
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

Open in new window

0
 
LVL 5

Expert Comment

by:sirplus
ID: 35026710
LOL
Ignore that -It doesnt use those 2 extra functions
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35041684
How did you go?
0
 
LVL 1

Author Comment

by:DJMohr
ID: 35041900
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
 
LVL 5

Expert Comment

by:sirplus
ID: 35042128
Post your book
Will set it up
0
 
LVL 5

Expert Comment

by:sirplus
ID: 35042134
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
 
LVL 5

Accepted Solution

by:
sirplus earned 2000 total points
ID: 35044930
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
:-)

I would add your other sheets to this workbook

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
 
LVL 5

Expert Comment

by:sirplus
ID: 35044949
nb
I left this file as automatic calculation but you could easily change it to manual calculation to make it more controlled
0
 
LVL 1

Author Closing Comment

by:DJMohr
ID: 35092150
Thanks man
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question