How to list folder content based on file types?

[kindlysinful69]

The following code will list all files in a network folder and works nicely for what I am doing but now the powers that be are requesting a bit more.

My current version allows me to look at one folder at a time and list the file data from that folder. Fairly basic folder and file structure, all file types are the same. My issue with the current code; Currently what the code does is start in cell A5 and going to F5 lists all file names, file path, file size, file type, date created, and date last modified contained in the defined network folder called out in cell C2. I can specify a date range to list start in C3 and end in C4. It performs this very well with a couple of exceptions. It will list duplicate file names and I would like to omit duplicates from the listed file names. It also will not list in ascending order based on the date created. I am still working on that myself but I need help on the next part. Please

On a different note what my boss would like is to be able to do the same with a few other network folders. I am not certain if my current base could work with some tweaking? The issue is these other network folders have a different folder/file structure and contain numerous subfolders with useless files to me. One example is the main folder is the current month, there could be hundreds of other folders inside this folder that have different job numbers associated with them, inside each job folder is a “SOJ” and “EOJ” folder along with pdf files, then inside these folders are several other folders with specific job task assigned to them, inside these folders, there can be jpg files within additional folders and other folders that contain “.id_comp”, “.is_comp”, “.is_comp,pdf” another folder “.is_comp_Exports”. What I am interested in, omitting file names with "_Summary" in their name, “jpg” files located in their folder and the csv files contained in the “.is_comp_Exports” folder.

Option Explicit

Sub ListFiles()

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim startDate As Date, endDate As Date

startDate = Range("C3").Value
endDate = Range("C4").Value

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Range("C2").Value)

Call GetFileDetails(objFolder, startDate, endDate)

End Sub

Function GetFileDetails(objFolder As Scripting.Folder, startDate As Date, endDate As Date)

Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim nextRow As Long

nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objFile In objFolder.Files
If objFile.DateCreated >= startDate And objFile.DateCreated <= endDate 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.DateCreated
Cells(nextRow, 6) = objFile.DateLastModified
nextRow = nextRow + 1
End If
Next

For Each objSubFolder In objFolder.SubFolders
Call GetFileDetails(objSubFolder, startDate, endDate)

Next

End Function

Thank you for any and all guidance.

 

[Micron]

Suggest you look into the Instr function. One example might be

If objFile.DateCreated >= startDate And objFile.DateCreated <= endDate Then  
   If Not Instr(objFile.Name,"_Summary") = 0 Then

If "_Summary" is in the file name Instr will return a number greater than 0. Not sure where you'd want to put such tests or how many you need. If many tests, one approach might be to call a function that uses a Select Case block based on True and if it returns True, write to the sheet(s) otherwise don't. Example

If objFile.DateCreated >= startDate And objFile.DateCreated <= endDate Then
   'up to you which denotes _Summary was found: T or F. I chose T
   If Not WordFound(objFile.Name) Then 'if not T then do stuff
      Cells(nextRow, 1) = objFile.Name
      etc.
   End If
End If

Function WordFound(strName As String) As Boolean
Select Case True
   Case Instr(objFile.Name,"_Summary") >0 '_Summary found
       WordFound = True
   Case Instr for next word test here >0
       WordFound = True
   and so on
End Select

Please enclose code in code tags (VBA button on posting toolbar) to maintain indentation and readability. The above is more guidance than full solution. HTH.

 

[kindlysinful69]

Pardon the delayed response.
Am having some personal issues and will try this out this week.
Thank you