referencing many closed workbooks

Hi Guys,

I have a workbook for each day recording energy usage from 52 meters,

I need to collate the data on a single workbook.

The only way i can think of doing it is to use the INDIRECT function but this will not work on closed workbook.

I need to sum the contents of B1:AW1 on the closed workbook and input that into a cell on the new sheet. then repeated for B2:AW2, B3:AW3 up to B52:AW52 once this has been done the process needs to be repeated for 31 workbooks.

The end product should be 31 Columns (one for each day ini the last month) and 52 rown (one for each meter) and the total daily consumption for each meter (sum of column B:AW)

I have no idea how this is going to happen but I think VBA may be the only solution.

thank you for reading and even more thanks for helping

This is not meant as a solution but just a start to give you an idea.
1. You need to create the array of workbook names,
2. You will need to control the starting row in the master where the sum will be entered.

Please Login or Register  to view this content.

You should provide a workbook with sample data. Since we don't know what your workbooks look like, the macro was just a hint of a possible solution.

The macro that I provided above is basically a loop. To use it, you will have to modify the line:

FilePath = "C:\Documents and Settings\Benjamin\Desktop\"

to reflect the drive and directories where your closed workbooks are found.

Next, the line:

WorkbookList = Array("the closed one")

would be modified to include the names of the workbooks you wish to open. Something like:

Please Login or Register  to view this content.

I think this may be close to what you are looking for.

Please Login or Register  to view this content.

Note that the subfolders must be in "C:\DIRECTORY"