Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    turn a list of names into folders (excel)

    I have a list of about 100 names 1 per row in column A starting at A2 to A102 that are to be made into folders under c:Bills(stored 100 individual folders)

    - i was wondering if there is some kind of macro which can make 100 folders in the path c:Bills
    - then in each folder make a folder called Totals

    manually i would go to c:bills and go new folder 100 times, then go into each folder and put a totals folder in each one.??

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: turn a list of names into folders (excel)

    How about this?

    Steve

    <pre>Option Explicit
    Sub CreateFolders()
    Dim sPath As String
    Dim rng As Range
    Dim rCell As Range
    sPath = "C:Bills"
    Set rng = Range("A2:A102")
    For Each rCell In rng
    MkDir sPath & rCell.Value
    MkDir sPath & rCell.Value & "Totals"
    Next
    End Sub</pre>


  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    Hi Steve Thanks,

    It works making the 1st folder name but when adding the "Totals" folder into the 1st folder i get a msg run time error '76':
    path not found at this line : MkDir sPath & rCell.Value & "Totals"
    ?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: turn a list of names into folders (excel)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>It works in all my tests
    The path should be there since you just created it 1 line earlier...

    What are the values in A2:A102?

    Steve

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    the values from a2:a7 are:
    it doesn't get past the first one

    591536 Alberta
    Acanthus
    Acclaim
    Action
    Advantage
    Alberta Clipper

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    I just pasted Steve's code into a workbook, and your list into A2:A7, and Steves code ran (modified to stop at A7) just fine and created all of the directories. Could you post a workbook that does not work for you?
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    sure

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    The strings in A2:A7 have a trailing space at the end of the string. This is causing a problem. Try changing the code to:

    <code>
    Sub CreateFolders()
    Dim sPath As String
    Dim rng As Range
    Dim rCell As Range
    sPath = "C:Bills"
    Set rng = Range("A2:A7")
    For Each rCell In rng
    MkDir sPath & Trim(rCell.Value)
    MkDir sPath & Trim(rCell.Value) & "Totals"
    Next
    End Sub
    </code>
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    Steve this is great. However, when I run it I get a VBA message box stating "Path/File Access Error". However, the folders and subfolders are created. Suggestions?

    Secondly, what if the user adds more line 103, 104 etc and tries to run it. In my limited test, new folders are not created. Is there a way to have this code run a second or third time and add only new folders?

    My VBA is limited. Each new example from people like you help to increase the knowledge. Thanks!

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: turn a list of names into folders (excel)

    For the error message: When do you get it, which line of code? Is the folder it is referring to hidden or inaccessible in some way?

    For the second item:
    <pre> <font color=red>Set rng = Range(range("A2", range("A65536").end(xlup))</font color=red>
    For Each rCell In rng
    <font color=red>On error resume next</font color=red>
    MkDir sPath & Trim(rCell.Value)
    MkDir sPath & Trim(rCell.Value) & "Totals"
    <font color=red>On error goto 0</font color=red>
    Next</pre>


    The new "red lines"
    The first will adjust the range for all the values
    The second will ignore an error when trying to create a duplicate
    The third will put error checking back on

    Steve

  11. #11
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    Thanks EVERYONE!!!!

  12. #12
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    The code runs and then the message appears. It does not refer to any line. When I use the Step Into feature the line Sub CreateFolders() is highlighted in yellow.

    HAven't had chance to do the extra code as of yet.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turn a list of names into folders (excel)

    Note that the set rng line which a copied from you response below into the code gives a Compile Error msg.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: turn a list of names into folders (excel)

    OOOPS. {I Should not do "air code"]
    instead of:
    <pre> Set rng = Range(range("A2", range("A65536").end(xlup))</pre>


    use:
    <pre> Set rng = Range(range("A2"<font color=red>)</font color=red>, range("A65536").end(xlup))</pre>


    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: turn a list of names into folders (excel)

    After starting to "Step Into", you use <F8> to go line-by-line until you get the error...

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •