Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make list from directory folder (excel 2000)

    hi lounge,

    i was wondering if any one has heard of a way get a name list of all ther folders in a folder

    i have a task to take 2006 folders about 200 of them and creating the same folder names 200 of them for 2007,

    is there an easy way through a macro to move into a dirctory c:test 2006 for example, load the folder name in column A down. then with a seperate macro create all the same folders in c:2007 using the name in col. a?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make list from directory folder (excel 2000)

    You don't need to put the names in a worksheet, you can create the folders directly without using any code:
    - Select Start | (All) Programs | Accessories | Command Prompt.
    - Type the following instruction, substituting the correct name:
    <code>
    XCOPY "C:Test 2006" "C:Test 2007" /T /E
    </code>
    - The /T /E switches make XCOPY copy only the folder structure, not the files.
    - Close the command prompt window.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make list from directory folder (excel 2000)

    Ah, good old DOS. Couldn't live without it!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make list from directory folder (excel 2000)

    For this purpose, it's definitely faster than writing a macro in VBA, even though that wouldn't be very difficult.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make list from directory folder (excel 2000)

    Hi there hans,

    Thanks for the dos command, although I would like a list so I can edit if needed before the folders are created. Also in the c:Test 2006 i have folders I dont want to create. So I thought bringing in a list into excel editing it if needed and then run another macro which makes the folder structure.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make list from directory folder (excel 2000)

    (Edited by HansV to provide link to post. It is very easy and much more user-friendly - see <!help=19>Help 19<!/help>)

    I did some searching and looks like it has been done in the forum

    <post#=548287>post 548287</post#>

    except no ability to make a list of folders in c:test 2006

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make list from directory folder (excel 2000)

    Here is a macro to list subfolders:

    Sub ListFolders()
    Const strParentFolder = "C:Test 2006"
    Dim fso As Object
    Dim fldParent As Object
    Dim fldChild As Object
    Dim i As Integer
    Range("A2:A65536").ClearContents
    i = 1
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldParent = fso.GetFolder(strParentFolder)
    For Each fldChild In fldParent.SubFolders
    i = i + 1
    Range("A" & i) = fldChild.Name
    Next fldChild
    Set fldChild = Nothing
    Set fldParent = Nothing
    Set fso = Nothing
    End Sub

  8. #8
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: make list from directory folder (excel 2000)

    The free "List Files" Excel add-in can make a separate list of folders
    as well as list all files (with hyperlinks) in specified folders/subfolders.
    Download from ... http://www.realezsites.com/bus/primitivesoftware
    No registration required.
    Jim Cone
    San Francisco, USA

Posting Permissions

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