Macro to extract list of PDF Files in a date range

[howard]

I need to extract a list of all PDF files on my C:\drive that are modified between 24/12/2022 to 05/01/2023 (date format dd/mm/yyyy)

I would like a macro to extract a list of pdf files in this date range and also show which directory these are in


Your assistance is most appreciated

 

[John_w]

You could do this with Power Query: Get Data -> From File -> From Folder. VBA isn't required.

 

[howard]

Thanks for the help

 

[jdellasala]

You could do this with Power Query: Get Data -> From File -> From Folder. VBA isn't required.

And once you've filtered the initial query to get only PDF files, you can load the list to a table:

WTF.xlsx
	A	B	C	D	E
1	Name	Date accessed	Date modified	Date created	Size
2	20180520.pdf	02/20/2023 11:17	06/12/2021 14:27	06/12/2021 16:13	128,066
3	20180527.pdf	02/20/2023 11:17	06/12/2021 14:30	06/12/2021 16:13	128,057
4	20180610.pdf	02/20/2023 11:17	06/12/2021 14:32	06/12/2021 16:13	127,523
5	20210115.pdf	02/20/2023 11:17	06/12/2021 15:03	06/12/2021 16:13	238,049
6	20210201.pdf	02/20/2023 11:17	06/12/2021 15:18	06/12/2021 16:13	328,149
7	20210217.pdf	02/20/2023 11:17	06/12/2021 15:24	06/12/2021 16:13	240,471
8	20210304.pdf	02/20/2023 11:17	06/12/2021 15:29	06/12/2021 16:13	258,566
9	20210322.pdf	02/20/2023 11:17	06/12/2021 15:37	06/12/2021 16:13	304,006
10	20210407.pdf	02/20/2023 11:17	06/12/2021 15:44	06/12/2021 16:13	293,961
11	20210425.pdf	02/20/2023 11:17	06/12/2021 15:48	06/12/2021 16:13	268,091
12	20210511.pdf	02/20/2023 11:17	06/12/2021 15:55	06/12/2021 16:13	288,890
13	20211229.pdf	02/20/2023 11:17	06/12/2021 16:01	06/12/2021 16:13	253,908
Sheet1

That uses this code which was generated entirely through the UI. I renamed Steps removing spaces so the code is clearer:

let
    Source = Folder.Files("C:\Documents\Receipts"),
    LowercasedText = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}), // Insures all Extensions are Lower Case
    FilteredRows = Table.SelectRows(LowercasedText, each [Extension] = ".pdf"), // Removes all except ".pdf" Extensions
    RemovedColumns = Table.RemoveColumns(FilteredRows,{"Content" /* Actual File */, "Extension", "Folder Path"}),
    ExpandedSizeAttributes = Table.ExpandRecordColumn(RemovedColumns, "Attributes", {"Size"}, {"Size"}),
    ChangedType = Table.TransformColumnTypes(ExpandedSizeAttributes,{{"Size", Int64.Type}}),
    SortedRows = Table.Sort(ChangedType,{{"Name", Order.Ascending}})
in
    SortedRows

Text after "//" or between "/*" and "*/" are comments. The File Size was Extracted in the ExpandedSizeAttributes line. There's a ton of Attributes there, but typically only Size is useful!

 

[howard]

Thanks for letting me know