How to list folder content based on file types?

kindlysinful69

New Member
Joined
May 1, 2023
Messages
2
Office Version
  1. 365
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Suggest you look into the Instr function. One example might be
VBA Code:
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
VBA Code:
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.
 
Upvote 0
Pardon the delayed response.
Am having some personal issues and will try this out this week.
Thank you
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top