Condensing several Excel spreadsheet averages into one sprea

Avenger

New Member
Joined
Oct 13, 2002
Messages
6
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
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

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
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

New Member
Joined
Oct 13, 2002
Messages
6
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
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top