+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

    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!

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

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

    Your post got me to delve into pattern matching. This worked on a small test...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

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

    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

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

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

    try
    Please Login or Register  to view this content.
    Last edited by jindon; 06-11-2020 at 05:56 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

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

    A solution without the loop
    Please Login or Register  to view this content.
    ❖ Please mark your thread is SOLVED if there has been offered a solution that works fine for you.

    ❖ If you like solutions provided by anyone, feel free to add reputation by clicking on ✶ Add Reputation bottom left of their posts.

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

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

    Quote Originally Posted by vba_madness View Post
    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.

  7. #7
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

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

    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...
    Quote Originally Posted by jindon View Post
    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

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

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

    ^ 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 \.

  9. #9
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

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

    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?

    Quote Originally Posted by huuthang_bd View Post
    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

  10. #10
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

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

    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

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,188

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

    Then you need to compare the time part like.
    Please Login or Register  to view this content.
    Last edited by jindon; 06-12-2020 at 09:57 AM.

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    1,901

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

    My one and a half cents worth...
    Please Login or Register  to view this content.

  13. #13
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    436

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

    Quote Originally Posted by vba_madness View Post
    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?
    Hi John,

    The information you have searched in the internet was right and you almost already understood how to the macro operated. However, I have something to note to you:
    The GetListFile function is the main function which you will use. The third param of this function is an option to get files in sub folders or not, please use False for this param if you don't want to get files in sub folders.
    The purpose of Test procedure is tested GetListFile function, you can use the function in another way.

    Huuthang

  14. #14
    Registered User
    Join Date
    08-30-2012
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2010
    Posts
    83

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

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA to search files within a folder for a specific string and return file name
    By tjej6987 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2014, 12:41 PM
  2. Search for a string on all excel files inside a ZIp folder
    By Moreno20 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-02-2014, 03:45 PM
  3. Search for string of text only within "*.log" files in the folder
    By harkirat777 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-01-2013, 10:43 AM
  4. [SOLVED] Excel VBA code for selecting a folder then search & open specified files in that folder
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 11:59 PM
  5. Want to search for string in list of files in a folder
    By DeepthiReddy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2012, 03:21 AM
  6. Case insensitive search for string within folder of excel files
    By vba_novice in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2011, 11:21 PM
  7. Search files in folder based on search string
    By djvice in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-22-2008, 01:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1