CODEWITHSUNDEEP
excelvba
7thGen
7thGen

Reputation: 63

Search for Specific Files in VBA

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

Upvotes: 0

Views: 332

Answers (1)

CLR
CLR

Reputation: 12289

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

Upvotes: 2

Related Questions