Grab filenames from directory and put into cells

Question

I need to grab all the filenames of a particular filetype in a folder, and put each filename into a separate cell, preferably going down by rows. I also need to make sure that there aren't any duplicate filenames and that this will work for any folder, as it's supposed to be dynamic.

Basically, I need to do something similar to this, but looped and I need to put in a check to make sure it's not already in the workbook: VBA Get File Name From Path and Store it to a Cell

I've tried using Application.GetOpenFilename, Dir() function, and some other stuff here:

count files in specific folder and display the number into 1 cel

Using Excel VBA to loop through .csv files in folder and copy the filename into cells in last column

Any help is appreciated, thanks!

the code I had is pretty barebones (new to programming) before I used other's code:

Sub Add_Policies()

'let user select folder, go into folder, grab all filenames which end in .htm, put each into a separate cell, one after the other.
'This needs to be dynamic, so probably put in an Update List button. Msgbox "x number of policies were added. There are now a total of y policies."
'check if policy is already present. if so, skip.
'add functionality to open a policy in excel



Dim fldr As FileDialog, nFiles As Integer, fldrName As String, FileDifference As Integer, FileName As String


    'Open the select folder prompt
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Users\Hothi\Documents"
        If .Show = -1 Then 'if ok is pressed
            fldrName = .SelectedItems(1)
        End If
    End With


If fldrName <> "" Then

    With fldrName
    path = fldrName & "\*.htm"

    FileName = Dir("path")

    Do While FileName <> ""
        nFiles = nFiles + 1
        FileName = Dir()
    Loop



    For i = 1 To nFiles
        Range("A3").Offset(i, 0) = nFiles
        Range.Value = Dir(



    Next

    If nFiles <> nFiles Then
        msgbox (" & FileDifference & number of policies added. There are now a total of & nFiles & policies.")
        Else: msgbox ("No new policies, check location of new policies.")
    End If



End Sub

Answer 1

The code to do this will look like this.

Sub GetFilesInFolder(SourceFolderName As String)

'--- For Example:Folder Name= "D:\Folder Name\"

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

Sub GetFilesInFolder(SourceFolderName As String, Subfolders As Boolean)

'--- For Example:Folder Name= "D:\Folder Name\" and Flag as Yes or No

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File
'Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    '--- This is for displaying, whereever you want can be configured

    r = 14
    For Each FileItem In SourceFolder.Files
        Cells(r, 2).Formula = r - 13
        Cells(r, 3).Formula = FileItem.Name
        Cells(r, 4).Formula = FileItem.Path
        Cells(r, 5).Formula = FileItem.Size
        Cells(r, 6).Formula = FileItem.Type
        Cells(r, 7).Formula = FileItem.DateLastModified
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"

        r = r + 1   ' next row number
    Next FileItem

    '--- This is the Function to go each and Every Folder and get the Files. This is a Nested-Function Calling.

    If Subfolders = True Then
        For Each SubFolder In SourceFolder.Subfolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub

Got to the link below, scroll down to the button named 'Download Now' and click that to download a copy of a utility that will do all the work for you, just as you described.

http://learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

Answer 2

One way is to reference the Microsoft Scripting dll into your project and use a scriptobject to get the directory. Then you would need to parse through the directory entries to get the files you want and put them in cells. I don't recall the specific commands to get the files as a scriptobject, but I am sure it is fairly well represented in various posts on this site and others. I didn't come up with the idea by myself, but I've done exactly what you are asking. I just don't remember which workbook it needed it for or I would post an example.

Another option is to open up a command prompt and break out good ol' DOS to feed a DIR command into a textfile that you can then use excel VBA to parse. I'm showing my age here, but I don't know enough about what you are really trying to do to know if this is a viable option or not.