CODEWITHSUNDEEP
vbaexcelexcel-formula
James Hayek
James Hayek

Reputation: 653

Populate Cells with Filenames in folder, Excel

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.

Upvotes: 1

Views: 7018

Answers (4)

Stephen Turner
Stephen Turner

Reputation: 7314

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

  1. Create a new Excel spreadsheet and save is as macro enabled (*.xlsm)
  2. On the Formulas tab click "Define Name" define a new name.
  3. Set Name: to "MyFiles" and Refers to: to =FILES(".\*.xls?")
  4. Starting with cell A1 enter a column of numbers from 1 to 20 (or so)
  5. Enter this =IFERROR(INDEX(MyFiles,A1), "") in cell B1
  6. 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?.

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

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

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

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

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

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.

Upvotes: 1

Related Questions