Open file within subfolders and loop

[brad999]

Evening,

I have the following code which opens a file within a subfolder of the activeworkbookpath, using the filename stored in cell B70. However, the file could be stored within 1 subfolder, or within multiple subfolders, so I need the code to look through all subfolders of the activeworkbookpath.

Also, ideally, it would be great if a loop could be in place that would open the filename at B70, extract the relevant information and paste to A1, then open the filename at B71 and extract relevant information and paste to A2 and repeat until cell B# is blank.

Is this possible?? Thanks in advance!!

Please Login or Register  to view this content.

[mikeTRON]

It is possible, are you familiar with loops? I'm on my phone or I'd send a sample workbook with something similar.

[FDibbins]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...ders-loop.html

[apo]

Hi..

This will do the following:

1. Search for each filename starting from B72 down in the activeworkbook path (and subfolders).

2. If it finds it.. it will copy the contents from Sheet1("A1:W1") to the next available row (looking at column A for that in the Activeworkbook).

3. Any files that are NOT Found will be shown in a Msgbox at the end.

Note: It will show the Command Window.. but don't worry.. it closes it each time it looks for a file...

Please Login or Register  to view this content.

[brad999]

Amazing apo!! That is impressive, and with only a short script! Could I possibly get your assistance on a slight modification...the code below has been updated with my amateur way of processing some of the data. However, I need the range("Y4:Z4") to change to ("Y5:Z5") after the first file is opened and data copied over, then to ("Y6:Z6") etc so it mirrors the ("Z4:Z" loop. The same with the range ("B4") changing to ("B5") etc if that makes sense!

Also, could all the pastes from your original code be done as values? I attempted to add PasteSpecial xlPasteValues but with no luck...

Finally, if I change all "Sheet1" to my tab of "Raw_data" I seem to get an error???

Thanks again apo!

Also apologies for the cross posting, i'll keep that in mind for the future,

Please Login or Register  to view this content.

EDIT: My 2nd attempt at that loop...interestingly, I ran this once and it successfully did everything for 2 files before showing error '1003'. The next time I ran , it worked for 3 files before the error. Then 4 etc! Not sure how I can fix that??

Please Login or Register  to view this content.