Populate Cells with Filenames in folder, Excel

Question

I am using Excel 2010

I would like to insert the file name of a list of files withing a certain folder location into an Excel cell.

I.e. if the file path is: C:\Users\NAME\Documents\FolderPath

and in the FolderPath I have several files of a certain type (File001.DAT ... File00N.DAT)

How can I populate all cells within a certain column (and starting at a certain row) with the file names *.DAT ?

Thanks in advance.

Update: I used command prompt to write file names to a text file. In command prompt I navigated to the directory in question:

CD /Directory/Of/FIles

and then I wrote the files to a text file as follows:

dir /b *.png > FIles.txt

the flag /b gives me names only. I then copied all the names and pasted them into Excel. It's not as robust as Bruce Wayne's solution but for the time being, it did what I needed.

Dont think excel-formula will cut it for you. I think this is squarely in the VBA domain. — Forward Ed, May 06 '16 at 16:07
Use `Dir`: http://www.techonthenet.com/excel/formulas/dir.php — John Coleman, May 06 '16 at 16:08
Look up the `Dir` VBA function - This might help start you on your way.... http://stackoverflow.com/q/10380312/1693085 and http://stackoverflow.com/a/10382861/1693085 — John Bustos, May 06 '16 at 16:08

score 2 · Accepted Answer · answered May 09 '16 at 15:33

2

Just retrieve the output from a DIR command via the standard output stream. Much faster than Dir$() and no need for any loops!:

Sub Foo()

Dim strFolderName   As String
Dim strFileType     As String
Dim pasteRange      As Range
Dim returnVals      As Variant

'// set parameters, change as required
strFolderName = "C:\Users\NAME\Documents\FolderPath\"
strFileType = "*.DAT"
Set pasteRange = Range("C5")

'// retrieve output of DIR command from CMD.exe
returnVals = Filter(Split(CreateObject("WScript.Shell").Exec("CMD /C DIR """ & strFolderName & _
                strFileType & """ /B /A:-D").StdOut.ReadAll, vbCrLf), ".")

'// Display results in chosen column
pasteRange.Resize(UBound(returnVals) + 1, 1).value = WorksheetFunction.Transpose(returnVals)

End Sub

answered May 09 '16 at 15:33

SierraOscar

17,507
6
40
68

I like this approach, I have been trying to read it and figure out each line. One line that I don't understand (and coincidently enough) doesn't work is under the comment '// Display results in chosen column I am getting a Runtime error '13' Type mismatch. Does this have something to do with an array not matching up? @macro-man – James Hayek Jun 03 '16 at 14:42
are you placing the results into a single column? (is the code _exactly_ the same as I posted above or have you changed something?) – SierraOscar Jun 03 '16 at 14:47
I changed the directory path to my directory only i.e. C:\Users\NAME\Documents\FolderPath\ That is all I changed – James Hayek Jun 03 '16 at 15:41
My apologies, I failed to include the final \ at the end of the directory path. It works as expected now. I am going to analysis, make adjustments and post a new thread with any troubles. Thank you!!! – James Hayek Jun 03 '16 at 15:43
No worries, don't forget to mark as the answer if it helped. – SierraOscar Jun 03 '16 at 16:35
Not sure how to do that; I up voted it though. That's the only button I see. – James Hayek Jun 05 '16 at 14:34
Just under the vote buttons on the left there will be a gray tick, you click this and it will turn green to confirm you've marked as the answer. – SierraOscar Jun 05 '16 at 15:07

score 1 · Answer 2 · answered May 06 '16 at 16:16

1

A quick and simple way is to use a loop that just checks the extension of each file in a directory:

Sub t()
Dim MyObj As Object, MySource As Object, file As Variant
Dim i&

file = Dir("C:\Users\me\Desktop\")
i = 1
While (file <> "")
    Debug.Print Right(file, 3)
      If Right(file, 3) = "dat" Then
         Cells(i, 1).Value = "found " & file
         i = i + 1
      End If
     file = Dir
  Wend
End Sub

Just adjust the Cells(i,1) as necessary.

answered May 06 '16 at 16:16

BruceWayne

22,923
15
65
110

Going to try this, thanks! For the time being, what I did was the following: Navigate to the directory in question using CMD and CD. I.e. CD /Locaton/To/Directory then I wrote the files to a Txt file like so: dir /b *.png > FIles.txt I then copied all the names in the list and pasted them into Excel. – James Hayek May 07 '16 at 16:23

score 1 · Answer 3 · answered May 06 '16 at 16:22

1

Another way - although the EnumerateFiles function is basically the same as BruceWaynes.

Sub PopulateSheet()

    Dim lRow As Long
    Dim colFiles As Collection
    Dim vFile As Variant

    With ThisWorkbook.Worksheets("Sheet1")

        'This will find the last row in column A, but
        'can use a static number or any other method to return a row number.
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        Set colFiles = New Collection

        'Remember to put the final \ in the file path.
        EnumerateFiles "C:\Users\NAME\Documents\FolderPath\", _
            "*.DAT", colFiles

        For Each vFile In colFiles
            .Cells(lRow, 1) = Mid(vFile, InStrRev(vFile, "\") + 1)
            lRow = lRow + 1
        Next vFile

    End With

End Sub

'//Places all file names with FileSpec extension into a collection.
Sub EnumerateFiles(ByVal sDirectory As String, _
    ByVal sFileSpec As String, _
    ByRef cCollection As Collection)

    Dim sTemp As String

    sTemp = Dir$(sDirectory & sFileSpec)
    Do While Len(sTemp) > 0
        cCollection.Add sDirectory & sTemp
        sTemp = Dir$
    Loop
End Sub

answered May 06 '16 at 16:22

Darren Bartrup-Cook

18,362
1
23
45

This code works quite well. I am currently investing time into this by trying to learn from it. Thanks! I do have two questions however, I will start with the most important first: How can I remove the file extension in the generation of file names? I tried removing several factors in the code to no avail. As for the second question, I will attempt to create a file dialog box to get the files independently and if no success, will post a new thread. – James Hayek Jun 03 '16 at 15:08
Btw, I was able to find how to remove file extensions using command prompt. It is as follows: dir /b /O:N *.DAT > flist.txt for %i in (*.DAT) do @echo %~ni >> fileNames.txt – James Hayek Jun 03 '16 at 15:22
Two ways I can think of to remove the extension: `mid(vfile,instrrev(vFile,"\")+1,instrrev(vfile,".")-instrrev(vfile,"\")-1)` or use a separate function: `Public Function GetBaseName(ByVal strFullName As String) As String: Dim oFSO As Object: Set oFSO = CreateObject("Scripting.FileSystemObject"): GetBaseName = oFSO.GetBaseName(strFullName): End Function` – Darren Bartrup-Cook Jun 03 '16 at 15:40
I always forget about the Dir parameters. – Darren Bartrup-Cook Jun 03 '16 at 15:43

score 0 · Answer 4 · answered Jun 27 '18 at 14:37

This can also be done without VBA though you do need a Macro-enabled workbook:

Create a new Excel spreadsheet and save is as macro enabled (*.xlsm)
On the Formulas tab click "Define Name" define a new name.
Set Name: to "MyFiles" and Refers to: to =FILES(".\*.xls?")
Starting with cell A1 enter a column of numbers from 1 to 20 (or so)
Enter this =IFERROR(INDEX(MyFiles,A1), "") in cell B1
Copy cell B1 to the down the column alongside the list of numbers, the reference in the formula should update automatically

You should get a list of the first 20 or so files.

The parameter for the FILES function is just a standard DOS wildcard pattern so you might want C:\Users\NAME\Documents\FolderPath\*.DAT instead of .\*.xls?.

Populate Cells with Filenames in folder, Excel

4 Answers4