Harlan wrote in message ...
"Rich" wrote...
I changed it to the following and got it to work. Thanks for all the
help everyone.

Public Sub CombineTextFiles()
Dim SrcFiles() As String, CurrSrc As String
Dim DestFile As String, Counter As Integer
Dim TextLine As String, sName As String, i As Long
Dim SrcTag As String, DestDir As String

ReDim SrcFiles(1 To 1000)
DestFile = "C:\ECPJM\combined.txt"
DestDir = "C:\ECPJM\"

On Error Resume Next
Kill DestFile

On Error GoTo 0
SrcTag = "C:\ECPJM\*.txt"
sName = Dir(SrcTag)

i = 0
Do While sName < ""
i = i + 1
SrcFiles(i) = sName
sName = Dir
Loop
If i 0 Then
' Need preserve or you lose everything added to the array
ReDim Preserve SrcFiles(1 To i)
Else
MsgBox "There are no files that match " & SrcTag
Exit Sub
End If

On Error Resume Next
Open DestFile For Output As #1
If Err.Number < 0 Then
MsgBox "Destination file '" & DestFile & "' cannot be opened. Check"
to make sure file exists."

MsgBox "Destination file '" & DestFile & "' cannot be opened. Check to
make sure file exists."
Exit Sub
End If
For Counter = 1 To UBound(SrcFiles)
On Error Resume Next
Open DestDir & SrcFiles(Counter) For Input As #2
If Err.Number < 0 Then
MsgBox "File '" & DestDir & SrcFiles(Counter) & "' cannot be"
opened. Check to make sure file exists."

MsgBox "File '" & DestDir & SrcFiles(Counter) & "' cannot be opened.
Check to make sure file exists."
Exit Sub
End If
Do While Not EOF(2)
Line Input #2, TextLine
Print #1, TextLine
Loop
Close #2
Next
Close #1

MsgBox "Complete.", vbInformation
End Sub


I changed the code to the following, but am getting a run-time error
76 "Path not found" on the line:
Open "C:\ECPJM\" & SrcFiles(Counter) For Input As #2

However the path is spelled correctly and I even copied it from
Windows Explorer. Any ideas?

The problem may be interpretting 'path'. By 'path' do you mean just the
"C:\ECPJM\" piece, i.e., the directory path? If so, that's one problem. The term
'path' in this context means the file's full path name. Try adding a statement
just above this like

Debug.Print Counter, "C:\ECPJM\" & SrcFiles(Counter)

to see what the file's full pathname appears to be to Excel/VBA. I suspect the
segment

Do While sName < ""
i = i + 1
SrcFiles(i) = sName
sName = Dir
Loop
ReDim SrcFiles(1 To i)

is the source of the problem. Try changing the ReDim statement to

ReDim Preserve SrcFiles(1 To i)