Macro to loop through subfolders within a root folder upto one level only

**Roop** · 01-13-2011, 01:59 PM

Hi Guys,
I am trying to write a macro to loop through all the subfolders in a main folder and open the latest excel workbook on the basis of latest modified date and time . I have something that does this but i have to manually change the subfolder name and file extension, I want it to look for files with .xlsx and .xls , when inside a subfolder. Any help will be highly appreciated.

Thanks
Roop

Here is what i have right now.

Please Login or Register  to view this content.

)

**Leith Ross** · 01-13-2011, 02:12 PM

Hello Roop,

Please enclose your code with code tags.

How to wrap your Code using the # icon

Select all your code using the mouse.
Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] After the last line.

**Roop** · 01-13-2011, 03:48 PM

I am sorry. Thanks Ross for letting me know

**Roop** · 01-14-2011, 09:43 PM

Needed urgent and much appreciated help.

**Leith Ross** · 01-14-2011, 11:16 PM

Hello Roop,

How many levels of subfolders do you want to search?
You said all, but does that include subfolders of subfolders?
Will the main folder location always be the same or do you need to browse for the folder?

**Roop** · 01-17-2011, 12:15 PM

Hi Ross,

I want to search only one level of subfolders i.e subfolders of a main folder upto one level only.
No, it does not include subfolders of subfolders.
Yes the main folder location will always be the same.
i.e T:\HVM LSP\F32\PCD\P1270\.

Thank you.

Roop

Originally Posted by Leith Ross

Hello Roop,

How many levels of subfolders do you want to search?
You said all, but does that include subfolders of subfolders?
Will the main folder location always be the same or do you need to browse for the folder?

**Leith Ross** · 01-17-2011, 04:47 PM

Hello Roop,

This macro wlll open the most recent ".xlsx" file found in the first level of subfolders of the main folder. If there are no ".xlsx" files found then the macro does nothing.

Please Login or Register  to view this content.

**Roop** · 01-17-2011, 09:17 PM

Hi Ross,
Thanks for the code, but i want it to open the most recent file in every subfolder i.e it goes to first subfolder say BT opens the most recent excel file in that subfolder and then goes to next subfolder say CTand opens the most recent file in CT and so on. Also i want to search for both ".xlsx" and ".xls" files as some subfolder have files saved with .xls and others have files with .xlsx, none of them has files with both extension. So, i want to go from subfolder to subfolder opening the most recent excel file in that subfolder, i should end up with no. of files open equal to no. of subfolders.

Thanks a lot.

Roop

Originally Posted by Leith Ross

Hello Roop,

This macro wlll open the most recent ".xlsx" file found in the first level of subfolders of the main folder. If there are no ".xlsx" files found then the macro does nothing.

Please Login or Register  to view this content.

**Leith Ross** · 01-17-2011, 10:57 PM

Hello Roop,

This amended macro will open the most recently modified ".xlsx" file in each sub-folder.

Please Login or Register  to view this content.

**Roop** · 01-18-2011, 07:29 PM

Hi Ross,
Thanks a lot man, code works perfectly. You have been a great help. One thing i wanted to ask just for future references is how can i add ".xls" along with ".xlsx" in the file type i am looking for using a macro or in other words make my macro look for files with ".xlsx" or ".xls" in subfolder.

Thanks again.

**Roop** · 01-19-2011, 05:29 PM

Hi Ross,
I am stuck on something using this code. I am trying to basically copy and paste the MostRecent opened from every subfolder into a new workbook, So the problem is the code copies and pastes some of the files twice. I am not able to figure out why. I can see that when opening the files, code goes back and forth sometimes between files. Can you help me here?

Here is what i am doing:-

Please Login or Register  to view this content.

**Leith Ross** · 01-19-2011, 10:34 PM

Hello Roop,

This macro will check the dates of only ".xls" or ".xlsx" files. The data from cell "E1" on worksheet "Difference Form" of the most recent workbook will be copied to the active sheet of workbook "Book1.xlsm" starting at cell "A1". The next most recent workbook will be copied to cell "A2", etc.

Please Login or Register  to view this content.

**Roop** · 01-20-2011, 10:01 PM

Thanks alot for all your help Ross.

Macro to loop through subfolders within a root folder upto one level only

LinkBack

Thread Tools

Rate This Thread

Display

Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Re: Macro to loop through subfolders within a root folder upto one level only

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions