Listing all the excel filenames from a folder..

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
Hi all,

I have hundreds of files (some PDF, some XLS) in folder J:\Documents

Is there a possibility in excel (I suppose thru VBA) to list all of the filenames found in J:\Documents to a worksheet?

Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Code:
Sub tester()
    With Application.FileSearch
        .LookIn = "J:\Documents"
       
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            Range("A1").Offset(i, 0).Value = .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
    End With

    
End Sub
 
Upvote 0
Greetings Patsy,

Assuming you have an Excel version prior to 2007, Makrini's code should work great :)

If 2007 however, I am advised that Application.FileSearch as gone away. Although I recently read a comment that FSO was also gone, I believe (and hopefully someone will correct me if wrong) that FSO still exists; in which case you might want to try:

In a Standard Module:
Code:
Option Explicit
 
Sub ListFiles()
 
Dim _
fso     As Object, _
fol     As Object, _
fil     As Object, _
rCell   As Range, _
i       As Long
 
'//                       change path to suit//
Const FILEPATH As String = "D:\031609"
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    If MsgBox( _
        "The sheet named: " & ActiveSheet.Name & " will have all the" & vbCrLf & _
        "filenames in " & FILEPATH & " listed in A2 and down." & vbCrLf & _
        "If OK, press < Yes > , else press < No >", vbQuestion + vbYesNo, "") _
            = vbYes Then
 
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fol = fso.GetFolder(FILEPATH)
        Set rCell = Range("A1")
 
        i = 0
 
        For Each fil In fol.Files
           i = i + 1
           rCell.Offset(i).Value = fil.Name
        Next
 
        rCell.EntireColumn.AutoFit
    End If
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Hope this helps,

Mark
 
Upvote 0
Thanks Makrini, works like a charm..although would be even perfect if it lists only the filename, instead of the whole filename& path i.e. presently it lists J:\Documents\File1.xls instead of just File1.xls.

Hi Mark...I am stil using Excel 2003 so no prob. Will copy and keep your code though in the event we shift to 2007 version..thanks !
 
Upvote 0
Easily done to strip out the path.

Code:
Sub tester()
    With Application.FileSearch
        .LookIn = "J:\Documents"
       
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            [COLOR="red"]tempbuf = .FoundFiles(i)
            tempbuf = Mid(tempbuf, InStrRev(tempbuf, "\")+1, 255)[/COLOR]
            Range("A1").Offset(i, 0).Value = [COLOR="Red"]tempbuf[/COLOR]
        Next i
    Else
        MsgBox "There were no files found."
    End If
    End With

    
End Sub
 
Upvote 0
Although I recently read a comment that FSO was also gone

I've not seen anything about this before ... just a rumor maybe ...?

Alex
 
Upvote 0
This is a great macro. What if you also wanted to get the file names and the date the file was last modified?
Sometimes they are revisions to files and sometimes people save the file with a slightly different file name but you can use the date to figure out which is more recent.
 
Upvote 0
Hi pto,

You could still use FSO, and simply offset the date modified:

In a Standard Module:
Code:
Sub ListFiles()
 
Dim _
fso     As Object, _
fol     As Object, _
fil     As Object, _
rCell   As Range, _
i       As Long
 
'//                       change path to suit//
Const FILEPATH As String = "D:\031609"
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    If MsgBox( _
        "The sheet named: " & ActiveSheet.Name & " will have all the" & vbCrLf & _
        "filenames in " & FILEPATH & " listed in A2 and down." & vbCrLf & _
        "If OK, press < Yes > , else press < No >", vbQuestion + vbYesNo, "") _
            = vbYes Then
 
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fol = fso.GetFolder(FILEPATH)
        Set rCell = Range("A1")
 
        i = 0
 
        For Each fil In fol.Files
           i = i + 1
           rCell.Offset(i).Value = fil.Name
           rCell.Offset(i, 1).Value = fil.DateLastModified
        Next
 
        rCell.EntireColumn.AutoFit
    End If
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Hope this helps,

Mark
 
Upvote 0
Thanks. I made a few changes using the offset for file path (Column a), file name (Col B) and date modified (Col C). The file path does not list any subfolders under the My Documents. Is there a way to add the subfolders under My Documents to the end of the file path.
<title>Excel Jeanie HTML</title>Sheet3

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 498px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Sub ListFiles()</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Dim _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>fso As Object, _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>fol As Object, _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>fil As Object, _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>rCell As Range, _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>i As Long</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>'// change path to suit//</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>Const FILEPATH As String = "C:\Documents and Settings\Owner\My Documents"</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td> Application.ScreenUpdating = False</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td> <td> Application.Calculation = xlCalculationManual</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td> <td> If MsgBox( _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td> <td> "The sheet named: " & ActiveSheet.Name & " will have all the" & vbCrLf & _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td> <td> "filenames in " & FILEPATH & " listed in A2 and down." & vbCrLf & _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">19</td> <td> "If OK, press < Yes > , else press < No >", vbQuestion + vbYesNo, "") _</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">20</td> <td> = vbYes Then</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">21</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">22</td> <td> Set fso = CreateObject("Scripting.FileSystemObject")</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">23</td> <td> Set fol = fso.GetFolder(FILEPATH)</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">24</td> <td> Set rCell = Range("A1")</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">25</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">26</td> <td> i = 0</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">27</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">28</td> <td> For Each fil In fol.Files</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">29</td> <td> i = i + 1</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30</td> <td> rCell.Offset(i).Value = FILEPATH</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">31</td> <td> rCell.Offset(i, 1).Value = fil.Name</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">32</td> <td> rCell.Offset(i, 2).Value = fil.DateLastModified</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">33</td> <td> Next</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">34</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">35</td> <td> rCell.EntireColumn.AutoFit</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">36</td> <td> End If</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">37</td> <td></td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">38</td> <td> Application.ScreenUpdating = True</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">39</td> <td> Application.Calculation = xlCalculationAutomatic</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">40</td> <td>End Sub</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

<title>Excel Jeanie HTML</title>Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 308px;"> <col style="width: 386px;"> <col style="width: 108px;"></colgroup> <tbody> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>C:\Documents and Settings\Owner\My Documents</td> <td>My Projects</td> <td style="text-align: right;">3/11/2008 16:32</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>C:\Documents and Settings\Owner\My Documents</td> <td>My Projects 2</td> <td style="text-align: right;">5/25/2006 13:25</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>C:\Documents and Settings\Owner\My Documents</td> <td>My Projects 3</td> <td style="text-align: right;">5/15/2006 20:13</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top