+ Reply to Thread
Results 1 to 10 of 10

Loop through folders and subfolders - Tweaking existing code

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Loop through folders and subfolders - Tweaking existing code

    Hello,

    I was able to find the macro to loop through folders and subfolders.

    I want to make a little adjustment on the code.

    The adjustments are:
    1. Count how many files need to check so that I can have the 'progress' status (OPTIONAL)
    2. Check if folder name consists of the string "Working", if it is, skipped, don't bother going into it. (MANDATORY)
    3. Take out an 'input box' that ask for File Name and Extension, but to automatically insert point 4 below.
      Please Login or Register  to view this content.
    4. Check for file name that consists of the string "P&L" follow by year (which is dynamic) and ends with EITHER ".xlsm" or ".xlsb" (MANDATORY)

    Below is the code

    Please Login or Register  to view this content.
    Last edited by dluhut; 07-21-2016 at 03:50 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Loop through folders and subfolders - Tweaking existing code

    Hello dluhut,

    I rewrote your macro using the Shell instead of the File System Object. I find the code to be cleaner and easier to maintain. The code shown here has been added to the attached workbook. There is button at the top of the sheet to run it.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: Loop through folders and subfolders - Tweaking existing code

    Hi Leith,

    Thanks for your help!

    I tried to run the macro by clicking on the button.

    However, I was only returning 7 files, and all those 7 files were from Feb and Mar folders.

    And, I didn't see for the Jun and July files, where most of it are supposed to be.

    I also noticed that it seems that it's pulling only the .xlsm files that have P&L file name in it, and skipping .xlsb files.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Loop through folders and subfolders - Tweaking existing code

    Hello dluhut,

    The problem may be with the file naming. I created both xlsb and xlsm files named as P&L 2016*.xlsb and P&L 2016*.xlsm and copied them to several different folders and disks. When the program ran, all files were found.

    I am running Office 2010 on Windows 7. Which version of Windows are you running?

  5. #5
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: Loop through folders and subfolders - Tweaking existing code

    Hi Leith,

    Sorry for the late reply...was bombarded with other stuff.

    I'm running 64 bit of windows 7 and Office 2013 (32 bit)...company only have license for 32 bit.

    As for the naming of the files, some were like "London P&L June 2016.xlsm" or "Europe P&L June 2016.xlsb" etc.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Loop through folders and subfolders - Tweaking existing code

    Hello dluhut,

    Okay, that confirms it is a naming match issue.

    Change this line in the macro...
    Please Login or Register  to view this content.
    To this... (Notice the * before the P&L)
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: Loop through folders and subfolders - Tweaking existing code

    It worked Leith!

    Thank you! Reps up to you (Please checked)!

    By the way, IF I want to check if a sheet name exist or not if it's already match the "Files.Filter 64, "*P&L*.xlsb;*P&L*.xlsm"" , where should I put it in the code?

    Say, after it has found to be "*P&L*.xlsb;*P&L*.xlsm", check if there's sheet names like "*PL-Res*" (notice the * in the beginning and end of string), if there is, then list it out, if not, ignore.
    Last edited by dluhut; 07-27-2016 at 05:19 PM.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Loop through folders and subfolders - Tweaking existing code

    Hello dluhut,

    Are you saying you want to search the worksheets of a matching workbook for any sheets matching "*PL-Res*" ?

    Where would the worksheet names be output?

  9. #9
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: Loop through folders and subfolders - Tweaking existing code

    Yes that's right.

    I don't need the output of the sheet, this way, the output of the file that either "*P&L*.xlsm or .xlsb" and in that file has a sheet name that matching "*PL-Res*", then output the filepath (full file path).

    I hope you understand what I'm describing

  10. #10
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: Loop through folders and subfolders - Tweaking existing code

    Leith,

    If you see my post, wondering if you'd be able to explain a little of your code?

    Tried to understand it and completely had no idea what it's doing.

    Not only that, I tried to get the 'Date Modified' and try to put it to column C. I can't get the date modified, not matter what I did, even though google says .DateModified

+ 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] creating folders and subfolders
    By Lohara005 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-13-2015, 07:28 AM
  2. Create folders in all the subfolders and move subfolders
    By Amarjeet Singh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2015, 12:51 PM
  3. [SOLVED] Extend Existing Code to Loop Through All Subfolders in a Folder
    By XOR LX in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2014, 08:47 AM
  4. Macro Code to Encrypt Multiple Excel Files in Folders and Subfolders
    By ali_1989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2014, 12:53 PM
  5. Loop trough all folders and subfolders and copy data
    By forfiett in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 10:22 AM
  6. [SOLVED] VBA code to add folders and subfolders
    By rhlittau in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2013, 06:57 PM

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