How to Search for all files in folder starting with a defined string

Hi There,

I am trying to return all files from a folder that meet a particular criteria.

In this case, the file naming convention being used is:
"ImportantFile_YYYY-MM-DD_HH-MM-SS.xlsx".

So for returning a list of Today's files, I would want to use the search string,
"ImportantFile_2020-06-10_" & * & ".xlsx"

I know that I can simply rip through all files in the folder and then use and if condition so that only those conforming to the entry condition are recorded, but I'm really asking if there is a more elegant way to do it!

The code below returns one match from the day, so can this be tweaked to return ALL files that meet the match conditions?

Please Login or Register  to view this content.

Thanks for reading my post, and hope you can help!

Your post got me to delve into pattern matching. This worked on a small test...

Please Login or Register  to view this content.

Hi Dangelor,

Thank you for your reply!

First of all, RegularExpression (RegExp) is totally new to me, but looks like it will be very useful for a whole raft of things that I've already done not to mention things in the future!

I've had a quick read up about it and have a basic understanding, but apologies if I have not understood correctly...

Please Login or Register  to view this content.

From what I can see,

Please Login or Register  to view this content.

should mean that it is looking for a pattern of 4 numbers (this corresponds to the year), followed by

Please Login or Register  to view this content.

2 digits for the month for the Month, then Day;Hour;Min;Second.
Can I ask, is this line of code saying,
Search for file starting with "ImportantFile_",

Please Login or Register  to view this content.

-> followed by a 4 digit number,

Please Login or Register  to view this content.

-> Not sure how to read this one... Does this mean (a) followed by a 2 digit number (b) followed by a "-" and then a 2 digit number OR (c) followed by "-" (which is pattern for a range), the range being a 2 digit number ?
I didn't see curly brackets {} referred to in the descriptions I looked at - is this simply a short hand for 2 digits? Could this

Please Login or Register  to view this content.

also have been written as the longer version of

Please Login or Register  to view this content.

?


This looks like it will return ALL files with ANY dates and times in this format.
What I am looking to return is ALL files with TODAY's date (or at least a specific date).
So, presumably I would change the expression from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

?

The normal way I would go about this would be to Compare the first part of the filename using Instr and then also check the string length and extension file type.
What are the advantages of this method?
Is it likely to be significantly quicker at sifting through large numbers of filenames?

Thanks,
John

Originally Posted by vba_madness

What are the advantages of this method?
Is it likely to be significantly quicker at sifting through large numbers of filenames?

Don't know enough about it to state any advantages, but looking at Huuthang_bd's use of an array to capture filenames, it would have to be a lot quicker than looping.

What I am looking to return is ALL files with TODAY's date

Jindon's pattern looks like what you want.

Thank you for responding, Jindon.
Your solution works well!

I am unfamiliar with the syntax of ".Pattern", so I was hoping that you might enlighten me as to some of the below...

Originally Posted by jindon

Please Login or Register  to view this content.

You have preceded "ImportantFile" with "^" - Does this simply mean that the file MUST START with the string, "ImportantFile" ? Similarly, must all of the following searches come after each other, or can they fall in any order?
For the TIME part of the filename, i.e. "HH-MM-SS", You have included this part,

Please Login or Register  to view this content.

in brackets "()". Is this because the first two time increments i.e. "HH" & "MM" are followed by "-", so "XX-" can be repeated twice ?
So effectively

Please Login or Register  to view this content.

creates "HH-MM-"
Then for the Seconds segment, this simply uses,

Please Login or Register  to view this content.

to create "SS"?
Finally, at the end, you have used "$". What is the purpose of this?

Please Login or Register  to view this content.

Thanks in advance,
John

^ means start of the string and $ means end of the string, so the string must

ImportantFile_2020_06_12_##-##-##.xlsx
where # means numeric character.[0-9]

If the pattern without ^ or $, it will match the string anywhere within a string.
e.g. it matches to

Please Login or Register  to view this content.

And . in the pattern means any character except line feed character, so you need to escape by adding escape character \.

Thank you, huuthang_bd - this is fantastic!

This looks an extremely fast way of retrieving the results (as dangelor has stated - Thanks too, dangelor!)

I have very limited exposure to command prompts - (each time that I use it, I feel like I am one step away from deleting the Internet!), so can I please ask a few questions regarding what you have done here?

Originally Posted by huuthang_bd

A solution without the loop

Please Login or Register  to view this content.

It appears that you create a temporary directory, sTmpFile, by asking the system to return a random name that is not being used.
This later creates the temp file in the current directory(?) (Presumably it does not matter what directory is specified as the temp file is deleted at the end)

You search for all filenames that match the search term, sSearch in the selected folder, sFolder and copy all filenames into the temporary file, sTmpFile.

You then copy all filenames that are held in the temporary file into the Active worksheet, starting in cell "A1".

Please Login or Register  to view this content.

So, from what I have Googled, this is what the above means....
DIR Displays a list of files and subfolders.
Syntax DIR [pathname(s)] [display_format] [file_attributes] [sorted] [time] [options]
/B Bare format (no heading, file sizes or summary).
/A:D Folder /A:-D NOT Folder
/S include all subfolders.
" >" Send Results to a temporary file

Does this mean, that by using "/S", it will also retrieve any files that are in a subfolder? This is probably something that I do NOT want as often I have a folder for old, or superceeded files, "SS". To avoid this, would I simply omit this term?

Thank you in advance for reading (and hopefully replying ),
John

Jindon,

Thank you for your further explanation, that's really helped me understand it much better!

May I ask one further question of your code...

I would want to select the latest time from that specific day. What would be the best way to achieve this?
So for the list below:

• ImportantFile_2020_06_12_10-05-10.xlsx
• ImportantFile_2020_06_12_16-00-10.xlsx
• ImportantFile_2020_06_12_09-36-20.xlsx
• ImportantFile_2020_06_12_11-54-10.xlsx
• ImportantFile_2020_06_12_07-45-52.xlsx

I would want this file selected:
ImportantFile_2020_06_12_16-00-10.xlsx

My one and a half cents worth...

Please Login or Register  to view this content.

Thank you Jindon, Huuthang_bd & Dangelour,

I feel that I have learnt a lot more than the one question that I asked, so much appreciated for all of your input and knowledge!

Now to mark this post as SOLVED!