Getting filenames into excel?

i.beard

New Member
Joined
Feb 12, 2009
Messages
13
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

New Member
Joined
May 10, 2007
Messages
19
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
This is right out of the VBA help file:

Code:
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:

Code:
dim i as long

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

i.beard

New Member
Joined
Feb 12, 2009
Messages
13
Thank you Chris I will try that now. And I am sensing an overwhelming need to own that book:)
 

i.beard

New Member
Joined
Feb 12, 2009
Messages
13
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?:confused:
 

Scott.C.Lyerly

Board Regular
Joined
May 28, 2009
Messages
70
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:

Code:
    ' 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:

Code:
        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? :)
 

Forum statistics

Threads
1,082,261
Messages
5,364,106
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top