How To Find Certain Files In VBA
by admin on Thursday, August 15th, 2019 | No Comments
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