Ok, I have ran into a large problem that I can't figure out. We have a
data machine that dumps a file at the end of the day with all the data
it has recorded for the day. It is decimal delimited format and I have
about 100 of these files with another one added each day. I need to
condense all this into one file that you can look at and find
information quickly. What I need to do is have each row represent a
day, and all the information from that day goes into the respective
columns for that day(Row). I originally thought I'd be able to to this
through the use of macros but after recording my own macro I have found
that there is no way to automate it to execute the same commands to
multiple files. I would appreciate any ideas anyone could offer because
my current situation with seperate files is very inefficient and harder
to search through.

Thanks,
Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Keith,

Do the individual files open as single row spreadsheets when opened by Excel?

HTH,
Bernie
MS Excel MVP


"mfx_krypt" wrote in message
...

Ok, I have ran into a large problem that I can't figure out. We have a
data machine that dumps a file at the end of the day with all the data
it has recorded for the day. It is decimal delimited format and I have
about 100 of these files with another one added each day. I need to
condense all this into one file that you can look at and find
information quickly. What I need to do is have each row represent a
day, and all the information from that day goes into the respective
columns for that day(Row). I originally thought I'd be able to to this
through the use of macros but after recording my own macro I have found
that there is no way to automate it to execute the same commands to
multiple files. I would appreciate any ideas anyone could offer because
my current situation with seperate files is very inefficient and harder
to search through.

Thanks,
Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

No, that would have made this alot easier to do. Each file consists of
about 300 rows, which I need to average before inputting them into the
condensed spreadsheet.


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Average in what way, and where does the resulting data go? Get specific.

HTH,
Bernie
MS Excel MVP


"mfx_krypt" wrote in message
...

No, that would have made this alot easier to do. Each file consists of
about 300 rows, which I need to average before inputting them into the
condensed spreadsheet.


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Just to add to Bernie's questions...

What do the input files look like. Decimal delimited sounds kind of vague
(well, to me).

Is it one value per line--just the number?
Is it two values per line--maybe a date and number?

Where are all the files located--in a dedicated folder with nothing else in
them. It might make it easier if that could be done. Then the macro could open
each file it finds in that folder.

How do you tell the date of the data--is it the name of the file????

Bernie Deitrick wrote:

Average in what way, and where does the resulting data go? Get specific.

HTH,
Bernie
MS Excel MVP

"mfx_krypt" wrote in message
...

No, that would have made this alot easier to do. Each file consists of
about 300 rows, which I need to average before inputting them into the
condensed spreadsheet.


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529


--

Dave Peterson

Ok, sorry for any confusion. The format of the text file is like this

09/22/05 12:30:21,1.34,3.27,0.345,0.0043,2.34
09/22/05 12:30:26,1.33,3.27,0.341,0.0050,2.39
09/22/05 12:30:31,1.45,3.31,0.401,0.0046,2.29

And it goes on, about 15 values per row and about 300 rows, each column
needs to be averaged for that day - so for example you would average
1.34,1.33,1.45 and get 1.373, you would than input that value into the
next spreadsheet and average the next column.

Also to answer the other question, file formats are in terms of dates -
something like 092205.HV2 (it has hv2 extension but its just a decimal
delimited text file). And yes they are in a dedicated folder.


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

mfx_krypt,

Try the macro below. Change the folder path to reflect the dedicated folder location.

HTH,
Bernie
MS Excel MVP

Sub CombineHV2Files()
Dim myCell As Range
Dim myName As String
Dim i As Integer

With Application.FileSearch
..NewSearch
'Change this to your directory
..LookIn = "C:\Documents and Settings\HV2 Files"
..Filename = "*.HV2"
..SearchSubFolders = False
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True
Set myCell = Range("B65536").End(xlUp)(2)
With myCell
..FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
..AutoFill Destination:=Range(myCell, _
myCell.Offset(-1, 0).End(xlToRight)(2)), _
Type:=xlFillDefault
End With
myName = ActiveSheet.Name
Cells(myCell.Row, 1).Value = Left(myName, 2) & _
"/" & Mid(myName, 3, 2) & "/" & Right(myName, 2)
myCell.EntireRow.Copy
With ThisWorkbook.Worksheets(1).Range("A65536"). _
End(xlUp)(2).EntireRow
..PasteSpecial Paste:=xlPasteValues
End With
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True

Next i
Else
MsgBox "There were no files found."
End If
End With

Cells.NumberFormat = "0.0000"
Range("A:A").NumberFormat = "mm/dd/yy"
Cells.EntireColumn.AutoFit
End Sub



"mfx_krypt" wrote in message
...

Ok, sorry for any confusion. The format of the text file is like this

09/22/05 12:30:21,1.34,3.27,0.345,0.0043,2.34
09/22/05 12:30:26,1.33,3.27,0.341,0.0050,2.39
09/22/05 12:30:31,1.45,3.31,0.401,0.0046,2.29

And it goes on, about 15 values per row and about 300 rows, each column
needs to be averaged for that day - so for example you would average
1.34,1.33,1.45 and get 1.373, you would than input that value into the
next spreadsheet and average the next column.

Also to answer the other question, file formats are in terms of dates -
something like 092205.HV2 (it has hv2 extension but its just a decimal
delimited text file). And yes they are in a dedicated folder.


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Thank you very much for this macro, I just have two questions about it.
First it is showing dates in the first column as 05/09/AS for all
cells, it isn't showing the day of the month. It probably just isnt
reading the format the date is in correctly. The files names look like
this: 050920AS.HV2, 050921AS.HV2. And in column A it is stored like
this: 9/20/2005 23:56, ect. I tired lookin through the code but couldnt
figure out what I needed to change to get the date to show correctly.

Also, is it possible to specify which columns it averages and imports
into the worksheet, I would like to leave out a few columns but if this
would complicate things too much than I'll just hide the ones I don't
need. Thanks for any info and thanks again for the macro.

Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Keith,

You had said that the files were named "something like 092205.HV2" which was mmddyy.HV2

When Excel opens that file, the tab name becomes 092205. Since the file is actually named "
050920AS.HV2", which is yymmddAS.HV2, then the tab name becomes 050920AS.

So, you need to change the lines:

myName = ActiveSheet.Name
Cells(myCell.Row, 1).Value = Left(myName, 2) & _
"/" & Mid(myName, 3, 2) & "/" & Right(myName, 2)

to

myName = Left(ActiveSheet.Name,6)
Cells(myCell.Row, 1).Value = Mid(myName, 3, 2) & _
"/" & Right(myName, 2) & "/" & Left(myName, 2)

You may need to rearrange some of those depending on your date system.

As for leaving out columns, the best thing would be to delete the unwanted columns at the end, with
a command like:

Range("B1,D1,F1").EntireColumn.Delete

which will delete columns B, D, and F (this is just an example....).

HTH,
Bernie
MS Excel MVP


"mfx_krypt" wrote in message
...

Thank you very much for this macro, I just have two questions about it.
First it is showing dates in the first column as 05/09/AS for all
cells, it isn't showing the day of the month. It probably just isnt
reading the format the date is in correctly. The files names look like
this: 050920AS.HV2, 050921AS.HV2. And in column A it is stored like
this: 9/20/2005 23:56, ect. I tired lookin through the code but couldnt
figure out what I needed to change to get the date to show correctly.

Also, is it possible to specify which columns it averages and imports
into the worksheet, I would like to leave out a few columns but if this
would complicate things too much than I'll just hide the ones I don't
need. Thanks for any info and thanks again for the macro.

Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Ok, thanks for the advice. I really appreciate your help this is going
to work perfectly for my situation.

-Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Sorry last question, I am trying to add some code to this so that with
each file it opens it also opens a file with a .CV1 extension, averages
the values and adds them onto the row of values stored in myCell.

I tried doing it but I can't figure it out quite yet...this vba is
getting addicting though.

Sub Load_HV2()
Dim myCell As Range
Dim myName As String
Dim i As Integer

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Test"
.Filename = "*.HV2"
.SearchSubFolders = False
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText Filename:=.FoundFiles(i), _
DataType:=xlDelimited, Comma:=True
Range("B1, G1, H1, K1, L1, M1, N1, P1, S1, T1, U1, V1, W1, X1, Y1, Z1,
AA1, AB1, AC1, AD1, AE1, AF1, AG1").EntireColumn.Delete
Columns("D:D").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Set myCell = Range("B65536").End(xlUp)(2)
With myCell
.FormulaR1C1 = "=AVERAGE(R1C:R[-1]C)"
.AutoFill Destination:=Range(myCell, _
myCell.Offset(-1, 0).End(xlToRight)(2)), _
Type:=xlFillDefault
End With
myName = Left(ActiveSheet.Name, 6)
Cells(myCell.Row, 1).Value = Mid(myName, 3, 2) & _
"/" & Right(myName, 2) & "/" & Left(myName, 2)
'NEW
WORKBOOKS.OPENTEXT FILENAME:=MYNAME & \"AS.CV1\", _
DATATYPE:=XLDELIMITED, COMMA:=TRUE
WITH MYCELL
.FORMULAR1C1 = \"=AVERAGE(R1C:R[-1]C)\"
.AUTOFILL DESTINATION:=RANGE(MYCELL, _
MYCELL.OFFSET(-1, 0).END(XLTORIGHT)(2)), _
TYPE:=XLFILLDEFAULT
END WITH
'NEW
myCell.EntireRow.Copy
With ThisWorkbook.Worksheets(1).Range("B65536"). _
End(xlUp)(2).EntireRow
.PasteSpecial Paste:=xlPasteValues
End With
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Application.DisplayAlerts = True

Next i
Else
MsgBox "There were no files found."
End If
End With

Cells.NumberFormat = "0.0000"
Range("A:A").NumberFormat = "dd/mm/yy"
Cells.EntireColumn.AutoFit
End Sub


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529

Ah, Nevermind. I have just figured it out.

-Keith


--
mfx_krypt
------------------------------------------------------------------------
mfx_krypt's Profile: http://www.excelforum.com/member.php...o&userid=22040
View this thread: http://www.excelforum.com/showthread...hreadid=469529