Search for Specific Files in VBA

Question

My VBA code below searches for all files in my Drive C: and list them in Sheet(1). Now, I am tasked to find specific .txt files only. I have tried modifying the code but to no success. I was thinking it has something to do with the ObjFile.

    Sub ListAllFiles()

Dim ObjFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = ObjFSO.GetFolder("C:\")


Call getfiledetails(objFolder)

End Sub

Function getfiledetails(objFolder As Scripting.Folder)

Dim objFile As Scripting.File
Dim nextRow As Long
Dim objSubFolder As Scripting.Folder

nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

For Each objFile In objFolder.Files
On Error Resume Next
    Cells(nextRow, 1) = objFile.Name
    Cells(nextRow, 2) = objFile.Path
    Cells(nextRow, 3) = objFile.Type
    Cells(nextRow, 4) = objFile.DateCreated
    Cells(nextRow, 5) = objFile.DateLastModified
    nextRow = nextRow + 1

Next objFile

For Each objSubFolder In objFolder.SubFolders
Call getfiledetails(objSubFolder)

Next

End Function

Any Help would be appreciated

Use DIR as shown [Here](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba/10382861#10382861) Change `StrFile = Dir("c:\testfolder\*test*")` to suit your need... — Siddharth Rout, Feb 28 '19 at 11:19

score 2 · Accepted Answer · answered Feb 28 '19 at 12:07

Using DIR probably has a slight performance advantage, but if you wanted to use your existing code then the following tweak to the getfiledetails sub would give you the desired output:

Function getfiledetails(objFolder As Scripting.Folder)

    Dim objFile As Scripting.File
    Dim nextRow As Long
    Dim objSubFolder As Scripting.Folder

    nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

    On Error Resume Next

    For Each objFile In objFolder.Files

        If objFile.Type = "Text Document" Then
            Cells(nextRow, 1) = objFile.Name
            Cells(nextRow, 2) = objFile.Path
            Cells(nextRow, 3) = objFile.Type
            Cells(nextRow, 4) = objFile.DateCreated
            Cells(nextRow, 5) = objFile.DateLastModified
            nextRow = nextRow + 1
        End If

    Next objFile

    For Each objSubFolder In objFolder.SubFolders
        Call getfiledetails(objSubFolder)
    Next

End Function

This can be added to, if you wanted more than one document type, such as..

If objFile.Type = "Microsoft Word Document" Or objFile.Type = "Text Document" Then

Sir, this works like a charm! THANK YOU!!!! I will check the DIR Function and follow your suggestion. Once again, Thank you! — 7thGen, Feb 28 '19 at 12:27
The reason I think that `DIR` might give you a performance boost is that the routine I've tweaked there is reading every file into an object and then examining the `.Type`. Using a `DIR` command such as `x = DIR(folder & "/*.txt")` does the filtering for you before hand. Both methods have their uses. Also, @Siddharth generally knows what he's talking about so if he's suggesting `DIR` then he'll have a very good reason. — CLR, Feb 28 '19 at 12:32

Search for Specific Files in VBA

1 Answers1