Condensing several Excel spreadsheet averages into one sprea

[Avenger]

At the Refinery where I work we use a data loger to log the different amounts of chemicals used in the refining process. Each Excel log file contains the data from 1 hour which is 60 data points(one for each minute). Each day it creates 24 Excel files(one for each hour in a day). Every month it creates 720 Excel files(24 x 30 days in a month). Every year it creates 8,640 Excel Files(720 x 12 months). I currently have 3 years worth or 25,920 Excel files to condense into one Excel File.

Here is what I need to do I need to calculate the average of all the data points. But I don't want to have to open each and every file of the 25,920 in order to get the averages.

Is there a macro I can write that will open each Excel file calculate the avg for all the data points insert it into my condensed Excel file and then close the open file and resart the process with the next file until all the files have been opened and averaged on my condesnsed Excel file?

Here is what I have so far for the macro:
Sub Test()
FileLocation = Range("C8").Value
FileName = Range("D8").Value
Workbooks.Open FileName:=FileLocation
Application.WindowState = xlMinimized
Windows("Condense.xls").Activate
Range("E8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=AVERAGE(FileName!R1C3:R60C3)"
'ActiveCell.FormulaR1C1 = "=AVERAGE(HR005448.CSV!R1C3:R60C3)"
Range("F8").Select
Windows("HR005448.CSV").Activate
ActiveWorkbook.Close
Windows("Condense.xls").Activate
End Sub

Anytime I try to replace the file name "HR005448.CSV" with the file names that are stored in cell D8 using the Variable FileName. When I run the macro it trys to have me open the file and and when I select the correct file it places a ref error in the cell where I want the average.

Any ideas would be greatly appriceated.
This message was edited by Avenger on 2002-10-15 08:54

 

[Avenger]

Looking for answers in all the wrong places.

 

[Iridium]

Not too sure how to do it exactly but if all the workbooks are in the same format the you should be able to use data consolidation (Data<Consolidate...)

Perhaps someone else could fill you in with a bit more detail

Chris

 

[Iridium]

On 2002-10-15 09:11, Chris Chattin wrote:
Not too sure how to do it exactly but if all the workbooks are in the same format the you should be able to use data consolidation (Data>Consolidate...)

Perhaps someone else could fill you in with a bit more detail

Chris

Missed a bit!

(Data>Consolidate...)

 

[Avenger]

Chris, thanks for the suggestion. We did find a solution. We copied all the CSV files into a monthly folder. Then in DOS used the the command "Copy *.* October.csv" this combined all the files into one. And now we have only 36 monthly files to work with instead of the 25,920 files we had before.

Thanks again for the Reply Chris