Fairly complicated excel problem

stereo_steve

I am completing a college thesis. Over the past five years a machine has been spitting out data in to excel files, I only need a fraction of the information in the excel document it has created though.

To be be exact I need one out of every six rows. Is there a function within excel to automatically delete the unrequired rows?

Each day the machine operated it created a folder and put that days data into it. Is it possible to merge all of these files together with the click of a few buttons?

I would really appreciate any help that you guys can give

Steve

youcancallmeal

A VBA macro could probably be written to do this but it would be fairly complex with so many files involved :eek:

alancool

I dont feel the macro need be that complicated.

The macro below I wrote for a colleague several months ago will handle any number of excel files. The thing it will not do is copy every 6 lines. What it will do is copy the sixth line from every excel file & paste into another excel file called C:\results.xls.

Im sure you could modify it to better suit your needs. If you do please post the code I'd be interested to seen how you do it (possibly with an additional loop).

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/09/2006 by Alan
'

'
Dim myFile
Dim myRef
'myRef = Range("l3").Value
myFile = Dir("c:\temp\*.xls")
Application.CutCopyMode = False
Application.DisplayAlerts = False
Do While Len(myFile) > 0
Application.CutCopyMode = False
Workbooks.Open Filename:="c:\temp\" & myFile
Range("A1:" & myRef).Select
Selection.Copy
ActiveWorkbook.Close
Workbooks.Open Filename:="C:\results.xls"
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A6:iv6").Select
ActiveWorkbook.Save
ActiveWorkbook.Close ' or process here
myFile = Dir
Loop

End Sub

Further Comments
Macro1 will:
Loop through each XLS file in C:\Temp
Open the first XLS file
Copy the contents of cells A6:IV6 (all cells in row 6)
Close the first XLS file
Open C:\Results.XLS
Paste the clipboard contents into the first unpopulated cell in column A
Save Results.XLS
Close C:\Results.XLS
Continue to the next iteration of the Loop I.E. the next XLS file in C:\Temp.

Alan:cool:

alancool

Sorry the macro in the last post was wrong. Here's the correction.



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/09/2006 by Alan
'

'
Dim myFile
Dim myRef
myRef = Range("l3").Value
myFile = Dir("c:\temp\*.xls")
Application.CutCopyMode = False
Application.DisplayAlerts = False
Do While Len(myFile) > 0
Application.CutCopyMode = False
Workbooks.Open Filename:="c:\temp\" & myFile
Range("A6:IV6").Select
Selection.Copy
ActiveWorkbook.Close
Workbooks.Open Filename:="C:\results.xls"
ActiveSheet.Paste
'ActiveCell.Offset(1, 0).Range("A1:J1").Select
ActiveCell.Offset(1, 0).Range("A1:" & myRef).Select
ActiveWorkbook.Save
ActiveWorkbook.Close ' or process here
myFile = Dir
Loop

End Sub

stereo_steve

Thanks alot guys.

I found a program http://www.sobolsoft.com/excelcsv/ which joined all the excel files I had into one large excel file. I'm now going to tinker around with themacro alancool posted.


I'll post up how it goes...

stereo_steve

AS it turned out I decided to use all the information and my results should just be more accurate. So after all that no macro!

I do have a new problem though. Something I'm sure someone could answer in 2mins!

I want to calculate a series of averages.

My first cell looks like this:

=AVERAGE(D3:D996)

I then want both numbers to be incremented by 992 and then the third cell to be incremented by 992 again etc

Any help would be really appreciated!