mattbnorris
New Member
- Joined
- Sep 17, 2019
- Messages
- 25
- Office Version
- 2016
- Platform
- Windows
Hello, I'm currently using the VBA below to create a list of files from the folder paths in C5 and C6 of my excel sheet. Is there a way I can adjust the code so it only returns files in the folder that contain a specific word?
VBA Code:
Sub ListFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolder As Object
Dim i As Integer
Dim fPathPrior As String
Dim fPathCurr As String
fPathPrior = Range("C5").Value
fPathCurr = Range("C6").Value
Range("B9:T10000").Value = ""
Range("B9:T10000").Interior.Color = vbWhite
On Error GoTo errHandler
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolderPrior = objFSO.GetFolder(fPathPrior)
Set objFolderCurr = objFSO.GetFolder(fPathCurr)
r = 9
'loops through each file in the directory and prints their names and path
For Each objFile In objFolderPrior.Files
Cells(r, 2) = objFile.Name
Cells(r, 11) = objFile.path
r = r + 1
Next objFile
r = 9
'loops through each file in the directory and prints their names and path
For Each objFile In objFolderCurr.Files
Cells(r, 12) = objFile.Name
Cells(r, 21) = objFile.path
r = r + 1
Next objFile
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "FOLDER PATH FOR NEW SHEETS DOES NOT EXIST!", , "Error"
Exit Sub
End Sub