make list from directory folder (excel 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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:

XCOPY "C:Test 2006" "C:Test 2007" /T /E

– The /T /E switches make XCOPY copy only the folder structure, not the files.
– Close the command prompt window.

Reply | Quote

Ah, good old DOS. Couldn’t live without it!

Reply | Quote

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.

Reply | Quote

(Edited by HansV to provide link to post. It is very easy and much more user-friendly – see Help 19)

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

post 548287

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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%5B/url%5D
No registration required.
Jim Cone
San Francisco, USA

Reply | Quote