Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel to folders

    Hey,

    So I have a list of items i want to turn into folders: A2-A140... Yes 138 folders
    I have looked up how to do this but it is not clear.

    Hopefully you can help.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I assume you mean that each of the 139 cells has a name that you want a folder named on your PC in some directory...is that right?

    I found some code that appears to do what you want:
    Code:
    Sub test()
    Dim fso As Object, mySir As String, temp As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    myDir = "c:\test\"
    On Error Resume Next
    For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
        Err.Clear
        temp = fso.CreateFolder(myDir & r.Value)
        If Err = 0 Then
            MsgBox temp & vbLf & " was created.", vbInformation
        Else
            MsgBox Err.Description
        End If
    Next
    Set fso = Nothing
    End Sub
    Last edited by RetiredGeek; 2015-12-08 at 16:31. Reason: Added Code Tags

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Iamm,

    Just as an FYI, the code KW posted will probably work just fine but you will need to add a reference to the Microsoft Scripting Runtime Library for it to work.

    As an alternative option, this code will achieve the same goal with no references needed. If the folder already exists, it will be ignored. Use the first code if the full path is provides as a cell value (ex. C:\Users\Maudibe\Desktop\junk). Use the second code if the cell values have just the folder name with no path (ex junk). You will need to replace my sample path in the code (in blue) with your desired path.

    HTH,
    Maud

    Full Paths as cell values
    Place in a standard module:
    Code:
    Public Sub SaveFiles()
    On Error Resume Next
    '----------------------------
    'DECLARE AND SET VARIABLES
        Dim rng As Range, cell As Range
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = ActiveSheet.Range("A2:A" & LastRow)
    '----------------------------
    'CREATE FOLDERS FOR EACH CELL VA;UE IN COL A
        For Each cell In rng
            MkDir cell
        Next
    '----------------------------
    'CLEANUP
        Set rng = Nothing
        Set cell = Nothing
    End Sub
    Folder names only as cell values
    Place in a standard module:
    Code:
    Public Sub SaveFiles()
    On Error Resume Next
    '----------------------------
    'DECLARE AND SET VARIABLES
        Dim rng As Range, cell As Range, path As String
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = ActiveSheet.Range("A2:A" & LastRow)
        path = "C:\Users\Maudibe\Desktop\"
    '----------------------------
    'CREATE FOLDERS FOR EACH CELL VA;UE IN COL A
        For Each cell In rng
            MkDir path & cell
        Next
    '----------------------------
    'CLEANUP
        Set rng = Nothing
        Set cell = Nothing
    End Sub
    Last edited by Maudibe; 2015-12-08 at 23:22.

Tags for this Thread

Posting Permissions

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