How To Find Certain Files In VBA

Someone wanted to loop through the files in over 100 directories and find a list of their powerpoint files.

This code will loop a specific directory and check if a file with a “pptx” extension exists in that directory. If it does, a message box will be displayed. Instead of the message box, the file’s path could be inserted into a database table called “tblPPTFiles”.

Private Sub FindPPTX_Files()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim intCounter As Integer
    Dim strFolder As String
    Dim objSubFolder As Object
    Dim strInsSQL As String
    Dim varFile As Variant
  
    'CurrentDb.Execute ("DELETE FROM tblPPTFiles")

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'root directory
    'strFolder = "S:\Share\OP_PrimDev\OBO_RMT\Companies\Caza Petroleum\Igloo 19 State\"
    strFolder = "C:\Users\erikloebl\Documents\access_references\a\"
    
    Set objFolder = objFSO.GetFolder(strFolder)
        
    For Each objSubFolder In objFolder.subFolders

       intCounter = 0

        Debug.Print " >>>> Subfolder: " & objSubFolder
        For Each varFile In objSubFolder.files
            Debug.Print varFile
            If Right(varFile, 4) = "pptx" Then
                'strInsSQL = "INSERT INTO tblPPTFiles (folder_name,folder,items) VALUES ('" & objFolder.Name & "','" & objSubFolder.Name & "','" & intCounter & "')"
                'CurrentDb.Execute strInsSQL
                MsgBox varFile
            End If
        
        Next varFile
        
    Next objSubFolder

    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

End Sub

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!