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
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