Select files from multiple folders that are nested 3 levels

[lhara2010]

Hi all,
I need to write a macro that can open up multiple excel sheets in multiple folders, perform a series of calculation and close those files. If it can perform the calculation without opening the book (to the user) that would be even better.

The excel files are located within two subfolders. The root folder could contain as many as 15 sub folders, and within ea sub folder would be two more folders, and within ea of those two folders would be one and then the excel file. (sorry this is difficult to write in words )

For instance:
.....\Root Folder\Sub folder 1\sub sub folder 1\folder\*.xls
.....\Root Folder\Sub folder 1\sub sub folder 2\folder\*.xls

.....\Root Folder\Sub folder 2\sub sub folder 1\folder\*.xls
.....\Root Folder\Sub folder 2\sub sub folder 2\folder\*.xls

....
....
....
and so on.

So far I have a macro that can allow me to pick a specific file, that will perform a series of calculation based on a recorded macro. But my macro only allows me to pick one file from the folder.

I would like to be able to include the above functionality to batch process a lot of data. I have ~80 excel files in each of the folder (last level) listed above, and my program currently only does calculation on one of those excel files.

I would greatly appreciate any help you experts can give me. I have dug through many forums and found snippets that does things close enough, but not to this level of flexibility. . I am a noobie in VBA and so the help portion is not very helpful.

Thanks in advance...

 

[wigi]

To summarize, here are my questions:
1. Running the program now lists all the files in the folder structure. I have both excel and another file format. I only care about the excel files. Separately I have a macro to perform an operation on each of those workbooks, how do I call that macro to perform this on every work book that is listed by sFullFilename?

2. I tried referencing the first workbook as sFullFilename(1),but was told this is not an array . So if this is just a string, how do I open each of these workbooks? or do I even need to do that?

Yes, they are listed. You need to test whether they're Excel files (probably just checking the extension being "xls", or "xlsx", or "xlsm". Typically that will suffice.

The macro that you have, will then just operate on each of the files. So the code of your macro (or at least the call to it) should be WITHIN my procedure "DoOneFile". In fact, I just had output in the debug window (Debug.Print sFullFileName) but you need to use that sFullFileName and pass it to your procedure.

For instance:

Call YourProcedure(sFullFileName)

where you have something like:

Sub YourProcedure(sFilename As String)
'code
End Sub