Masters Of The VBA universe I have another Question 1

[addicted2]

OK i have a folder on C: called HOUSEHOLD within this folder I have several subfolders and within these subfolders I have a minimum of 3 xls files. So that I can populate a listbox and trap errors I need assistance with a code that will Search all the subfolders and fill column c of the active workbook with a list of all the xls files that reside in these folders. Ie


C:HOUSEHOLD

FOLDER1 FOLDER2 FOLDER3

XLS1 XLS2 XLS XLS1 XLS2 XLS1 XLS2 XLS3 XLS4


The code will need to clear the column prior to entry as the amount of folders and files will fluctuate on a regular basis.

Oh man I hope I explained that ok. I anyone can make any sence out of what I need I would once again realy appreciate your help.

Cheers
Tony X-)

 

[SkipVought]

Tony,
Here's a problem, maybe, with what you want. What if the same workbook name turns up in 2 different folders? You will have duplicate names.

How 'bout including the sub-folder name in the adjacent column?

Sub Main()
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("D:\Household")
    Set fc = f.SubFolders
    For Each f1 In fc
        FolderDrill f1, f1.Name
    Next
End Sub
Sub FolderDrill(fld, sht)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(fld)
    Set fc = f.Files
    For Each f1 In fc
        With ActiveCell
            .Value = f1.Name
            .Offset(0, 1).Value = sht
            .Offset(1, 0).Select
        End With
    Next
End Sub

Let me know Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

... and what are you gonna be doing with your list? Skip,
metzgsk@voughtaircraft.com

 

[addicted2]

Yes That it is a good idea Skip to put the code in the adjasent coloumn. The List well If I am doing this right I hope to use the list for 2 purposes 1. to populate a list box that will be used when the folder is udated. and 2. To trap errors so that the name is not entered in twice.

Thats it in theory anyway. God knows when I have finished this I might find that I have discovered the formula for some rocket fuel. B-)

Regards and thanks for your time.

Tony