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.

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.

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?

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.

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.

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?