+ Reply to Thread
Results 1 to 4 of 4

Loop is to slow: Need to list files meeting criteria within sub-directories

  1. #1
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Post Loop is to slow: Need to list files meeting criteria within sub-directories

    I'm struggling with a piece of code that takes 8 minutes or more to run and I'm looking for a faster solution.

    In the code, "FullPath" is the directory within which I need to look. Undeclared variables are global.

    Purpose of code:
    1. within the target directory, find folders which have been modified within a date range
    * The directory has over 10,000 folders inside.
    2. Search each identified folder (1 level deep) for a spreadsheet that contain "abcde" in the name.
    3. if found, add file details to arrays for later processing.

    I've seen some ideas using Dir to speed up loops, but I can't see how to use it in my situation, since I'm looking at sub-directories for files.

    Any ideas are appreciated.

    Please Login or Register  to view this content.
    Last edited by Suicyco; 08-01-2019 at 06:39 PM. Reason: figured it out

  2. #2
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Loop is to slow: Need to list files meeting criteria within sub-directories

    forgot to mention: the 8 minutes is a starting point, if searching a small date range. I believe it grows quickly with a larger date range, but I only know for certain that the entire program (including mining data from files) takes days if left to process the entire directory.
    Last edited by Suicyco; 08-01-2019 at 03:11 PM.

  3. #3
    Registered User
    Join Date
    11-06-2012
    Location
    Texas
    MS-Off Ver
    O365
    Posts
    27

    Re: Loop is to slow: Need to list files meeting criteria within sub-directories

    In my experience, it's going to be slow no matter what you do. You can also use Get&Transform (PowerQuery) to pull a list of all files in the folder and filter from there. That is probably your fastest option. In a recent project, I think I was around 3-5 seconds per file (local network folder) to find, open, copy data, and close.

  4. #4
    Registered User
    Join Date
    12-02-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Loop is to slow: Need to list files meeting criteria within sub-directories

    Daishiknyte,
    Thanks for the response. I will look into the PowerQuery option, but for now I found a solution using Dir that cut the time from 8 minutes to under 3, most important with no danger of the time growing as directories are added.

    I tried to paste the code in, but got blocked by some virus protection system at ExcelForum. Not sure why.

+ 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. [SOLVED] Count Data Meeting Criteria Across Columns Meeting Criteria
    By idelta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2015, 03:53 PM
  2. Open multiple files and copy data is slow, code after end of loop not working - help?
    By hadamhiram in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2014, 11:52 PM
  3. Count data meeting certain criteria from multiple files and compile into Summary
    By Kettie in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2013, 12:30 PM
  4. VBA Macro to Loop and Merge Files in Several Directories
    By sanjeevpandey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-18-2013, 10:53 AM
  5. Replies: 2
    Last Post: 03-30-2011, 08:05 PM
  6. Move a list files to different directories
    By Tellm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2010, 08:03 AM
  7. Loop to delete rows meeting criteria
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2009, 04:54 PM

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