Getting filenames into excel?

[i.beard]

I have about 10,000 files that I need to create an excel spreadsheet containing all of their names. Is there a way to just pull the filenames into excel without actually pulling the actual file in. Basically if I open my folder can I get the names of all of the files in that folder into excel?

 

[Bean Counter]

If you own Mr. Excel's "VBA and Macros for Microsoft Office Excel 2007" the exact code you need is on pages 114-115. I don't know if I would violate the copyright if I posted it here.

 

[ChrisM]

This is right out of the VBA help file:

Sub ShowFileList(folderspec)
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 in fc
        s = s & f1.name 
        s = s &  vbCrLf
    Next
    MsgBox s
End Sub

where folderspec = your folder path, as a string, ie, "C:\Myfolder"

The For Each loop is where all the file names are, just do something like this:

dim i as long

i = 5
For Each f1 in fc
       MyWorksheet.Cells(i,1) = f1.name
       i = i +1
Next f1

 

[i.beard]

Thank you Chris I will try that now. And I am sensing an overwhelming need to own that book

 

[Scott.C.Lyerly]

We just this discussion in another thread! How fortuitous!

http://www.mrexcel.com/forum/showthread.php?t=393011

 

[i.beard]

As I am very very very new as in this is my first time using macros, I am at a loss. Can you possible dumb it down a bit for me?

 

[Scott.C.Lyerly]

I can explain this part of the thread I mentioned earlier:
http://www.mrexcel.com/forum/showpost.php?p=1951156&postcount=3

What this does is it takes the directory you specify (something "C:\" or "C:\Program Files\", etc) and loops through every file in that directory.

As it loops through this code, it adds each file it finds to an object known as a collection. What a collection is is exactly what it sounds like: a collection of "things". In this case, we're adding file names to the collection. Every time we add one, the collection grows bigger (naturally!).

Okay, so that's that part. I commented the code in the thread at the link above pretty heavily, and hopefully in plain enough english to be fairly easily understood.

Now, the piece not in that thread ('cuz it wasn't applicable) was the piece where you write it to a worksheet. What makes the collection great is that it's very easy to work with (easier I find than an array).

So, after the first For/Next loop in the code in the above thread, you want to do something like this to get it into a worksheet:

    ' This is the part where we write the file names
    ' to a worksheet.
    For i = 1 To coll.Count
        ' We're choosing this workbook and the very generic Sheet1
        ' to write our file names out to.  Presuming you want to start
        ' at row 2 (leaving row 1 for a header), then we simply
        ' fill in the cells(row,col) with the items in the collection
        ' by iterating through it with a For/Next loop.
        ThisWorkbook.Sheets("Sheet1").Cells(i+1, 1).Value = coll(i)
    Next i

Bare in mind, you can change the target workbook, worksheet, and range where you want these file names to go by changing the elements of this line:

        ThisWorkbook.Sheets("Sheet1").Cells(i+1, 1).Value = coll(i)

In addition, if you want to get "fancy" you can filter for the type of files you want by applying some kind of If statement or Select/Case to the For/Next loop that runs through the directory. But maybe that's getting ahead of ourselves?