How to Filter Excel Files by Creation Date Range Using VBA
Question details
The user needs a VBA macro to list files from a directory and its subfolders that were created between a specified start date and end date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Listing and managing files based on their creation timestamps for reporting, organization, or data extraction.
- Observed behavior
- The user wants to dynamically read the start and end dates from spreadsheet cells and output the filtered file details using a recursive folder procedure.
Ensure you have your start date inputted in cell C3 and your end date in cell C4 of your active worksheet, and verify that the Microsoft Scripting Runtime library is enabled in your VBA editor references if you plan to use FileSystemObject early binding.
Create a Recursive VBA Macro to Filter Files by Date Range
Use a recursive procedure with FileSystemObject to check the DateCreated property of each file and list only those within the timeframe defined in cells C3 and C4.
This approach uses recursion to dive into subfolders, checking each file's creation date against the dates specified in your spreadsheet.
By utilizing the DateAdd function, the macro ensures the end date encompasses the entire final day, preventing files created late on the end date from being excluded.
In your main macro, read the start date from cell C3 using `startDate = Range("C3").Value` and the end date from cell C4 using `endDate = Range("C4").Value`.
Create a recursive sub-procedure that accepts the target folder path (or FileSystemObject folder object), the start date, and the end date as arguments.
Inside the file loop of your procedure, add the condition: `If objFile.DateCreated >= startDate And objFile.DateCreated < DateAdd("d", 1, endDate) Then`.
If the condition is met, write the file's properties (such as name, path, and creation date) to the desired rows in your active worksheet.
Loop through `objFolder.SubFolders` and call the same recursive sub-procedure from within itself, ensuring you pass down the exact same start and end date variables.
Use WPS Spreadsheet to Run Your File Filtering Macros
WPS Office fully supports VBA and macros, allowing you to easily execute advanced scripts like filtering files by date ranges. You can automate your repetitive data gathering tasks directly within WPS Spreadsheet without needing expensive software.
- 1. Open your macro-enabled file: Launch WPS Spreadsheet and open your existing .xlsm file containing the date filtering macro.
- 2. Access the Developer tab: Navigate to the 'Developer' tab on the top ribbon. If it is hidden, enable it from the WPS options menu.
- 3. Run the Macro: Enter your start and end dates in cells C3 and C4, click on 'Macros' in the Developer tab, select your filtering script, and click 'Run'.
Frequently Asked Questions
Why does the VBA macro skip files created on the end date?
If you use a strictly less-than operator or an exact date match for the end date, files created later on that exact day (with a timestamp after midnight) might be excluded. Using `DateAdd("d", 1, endDate)` ensures the macro captures all files up to the very last second of the end date.
Can I modify the macro to filter by 'Date Last Modified' instead of 'Creation Date'?
Yes. In your VBA script, simply replace `objFile.DateCreated` with `objFile.DateLastModified` in your conditional If statement to filter based on when the files were last changed.
Do I need to enable any specific VBA references to use this method?
If you are using early binding with the FileSystemObject (declaring variables as specific FSO types), you must enable the 'Microsoft Scripting Runtime' reference in the VBA editor via Tools > References. Alternatively, you can use late binding (`CreateObject("Scripting.FileSystemObject")`) to avoid setting references manually.
