Recursing sub-folders to open 1 XL file, copy 1 WS to MstrF

[lionelnz]

Hi all.

I need a bit of help in writing code to recurse sub-folders, open the only XL file in there & copy the master sheet of that file (which has the same name as the sub-folder) to a master summary file.

Here is the over all scenario.

I am the financial manager for a small company and I down load our bank accounts, visa accounts & payments made by direct credit (DC’s) thru Internet Banking as CSV files. This is no problem as I have macros that import & format the data as required to the requirements of the account type.

The DC’s are slightly different as I may have up 10 CSV files per month, one per transaction which may have up to 30 creditors per transaction. I use a macro to import the CSV file/sheet into a summary XL file for the month.

Here is my challenge – I now want to summarise these monthly summaries into one annual summary for collation & reporting purposes.

To do this I need to the following - But I am not sure if this “pseudo-code” is possible or I am on the right track.


1. Open the Master annual summary (MAS) file
2. ascertain & record in an array the all the subfolders
3. Loop thru the subfolders & open the required work book
4. Copy the summary sheet (the copied CSV sheets are still in the summary file) of the workbook
5. Close the monthly summary workbook
6. Paste the sheet into MAS
7. After looping thru & save the MAS.

But I am not sure if this “pseudo-code” is possible or I am on the right track.

I have code that recurses & lists files, copies files, backup files, rename files, but not actually open files in the sub-folders & do things by looping thru.

I think I read some where that an array cannot actually open files in subfolders but you loop thru the sub-folders, use the array to go to the individual sub-folders and open the file to do stuff.

Added info - the structure I have is like this

Folder structure - DCPayments\08 (where 08 is August & all the folders are named as mm & the summary file is “DC0708.xls”
Cheers

Lionel Downunda

[westconn1]

do a search for recursive dir or fso
i posted both in one thread a while ago, and there are many other examples

probably didn't read your post well enough, you can do all you asked, in the recursive loop once you find the file you want you can just exit the loops and open the filename using the last folder path from the loop,

[lionelnz]

Thanks for this. I knew there would be something there & probably will need help finetuning once i get to write it.

[westconn1]

if you are seaching mutiple files in different folders, you can either open the files as you find them, then continue the search after processing each, or find all saving the path and filename into an array, then process all the files in the array