VBA Help - List only File Names from Criteria and Specific File Type

[johnny51981]

Hello:
I plagiarized the following VBA from a YouTube video that I found, and it gives me exactly what I am needing...however it is missing a piece that I hope can be solved here. I would appreciate any help, and please know...I am not very strong in VBA (yet).

I am needing to augment this code to only return file names that contain the words "WORK ORDER" and that are saved as a PDF file type.

Option Explicit
Sub ListAllFiles()
    
    ThisWorkbook.Sheets("Files").Select
            
    Call ClearList
    
    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(Range("NTPFolder"))
    
    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
    Cells(nextRow, 1) = objFile.Name
    Cells(nextRow, 2) = objFile.Path
    Cells(nextRow, 3) = objFile.Size
    Cells(nextRow, 4) = objFile.Type
    Cells(nextRow, 5) = objFile.Attributes
    nextRow = nextRow + 1
Next

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

End Function
Sub ClearList()
'
' ClearList Macro
'

'
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A2:E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A2").Select
End Sub

 

[Kov4n]

This works with one tiny change, instead of using "&", I changed it to "And", which gave me the result I was looking for. Appreciate it!

Hi, using this code is there a way to get only .xlsx files from a folder, I’ve tried the above code removing the file info name but the macro just runs and lists nothing??

 

[JoeMo]

Maybe:

For Each objFile In objFolder.Files
If objFile.Name Like "*WORK ORDER*" and LCase(Right(objFile.Name, 5)) = ".xlsx" Then
Cells(nextRow, 1) = objFile.Name
Cells(nextRow, 2) = objFile.Path
Cells(nextRow, 3) = objFile.Size
Cells(nextRow, 4) = objFile.Type
Cells(nextRow, 5) = objFile.Attributes
nextRow = nextRow + 1
End If
Next

 

[Kov4n]

Maybe:

For Each objFile In objFolder.Files
If objFile.Name Like "*WORK ORDER*" and LCase(Right(objFile.Name, 5)) = ".xlsx" Then
Cells(nextRow, 1) = objFile.Name
Cells(nextRow, 2) = objFile.Path
Cells(nextRow, 3) = objFile.Size
Cells(nextRow, 4) = objFile.Type
Cells(nextRow, 5) = objFile.Attributes
nextRow = nextRow + 1
End If
Next

The aim is first to count the .xlsx files in a folder and state the number in a defined cell, then in a table below on the same sheet, it lists files from selected folders....
Counting the files is fine but when listing .xlsx files from a selected folder it lists all the files regardless of the type....

This is currently the code I'm using, wanted to see if there is a way to list .xlsx files only?




Sub Outstanding39()

'Count files from selected folder
Dim folder_path As String
Dim strtype As String
Dim totalfiles As Variant

strtype = "*.xlsx*"

folder_path = Worksheets("Data2").Cells(83, 2).Value

If Right(folder_path, 1) <> "\" Then folder_path = folder_path & "\"
totalfiles = Dir(folder_path & strtype)

Dim i As Integer

While (totalfiles <> "")
i = i + 1
totalfiles = Dir
Wend

Worksheets("Open").Cells(15, 7).Value = i
Worksheets("Open").Cells(15, 7).Select

'List Files from selected folder
Dim objFSO As Scripting.FileSystemObject
Dim objFile As Scripting.File
Dim objFolder As Scripting.Folder
Dim nextRow As Long

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Worksheets("Data2").Cells(83, 2).Value)

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

For Each objFile In objFolder.Files
Cells(nextRow, 2) = objFile.Name
Cells(nextRow, 16) = objFile.ParentFolder
nextRow = nextRow + 1

Next

End Sub