It looks like that your text files are coming from UNIX (or MACs). The use a
different end of line marker than DOS/Windows text files.

Lines in DOS text files usually end with a carriage return followed by Line
feed.

In Unix land, they end with only a linefeed.

So either you don't have any end of line marker or you have the wrong end of
line marker (difficult to tell in your message).

You can open the file in a text editor that can show hex characters (I use
UltraEdit (http://www.ultraedit.com), but there are thousands of free editors
out there) to verify what you have.

If your files have the Unix end of line markers, there are also thousands of
converters available.

If your files don't have any end of line markers, I'm not sure if there are any
programs available to add them--but you could open the file and hit enter at the
end of the (each???) line.

But it sure looks like you have a combination of file types out there.

=======
Or maybe you could just let excel try to do the work (untested):

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim DestCell As Range
Dim wkbk As Workbook

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.csv")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
'hours.csv
If LCase(myFile) Like "*hours.csv" Then
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr 0 Then
Set DestCell = Workbooks.Add(1).Worksheets(1).Range("a2")
DestCell.Parent.Range("a1").Resize(1, 3).Value _
= Array("Month", "Age", "Name")
For fCtr = LBound(myNames) To UBound(myNames)
Set wkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
ActiveSheet.UsedRange.Copy _
Destination:=DestCell
With DestCell.Parent
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
wkbk.Close savechanges:=False
Next fCtr
End If

End Sub




nickysquawkes wrote:

Thanks for the advice. I am nearly there. I have got the batch file
setup doing the following.

copy header.txt allhours
copy *hours.csv allhours.txt

Which produces the following result:

Month,Age,NameFeb,48,Mike
Feb,57,Sarah
Feb,19,BurtJan,16,Ted
Jan,96,Dave
Jan,91,ElgarMar,56,Kate
Mar,39,Vciky
Mar,67,Susan
--

Dave Peterson