Concatenating or Appending Text Files

[jtp550]

"I have several(sometimes more than 2) text files I import into exce
and with a couple of macros format the info. What I would like to kno
- How do I append those text files on to the end of each other the
import the big text file into excel. Excel 2002 makes several Workbook
then I have to use the macros to copy and paste, gets to be a pain whe
I have 50 workbooks.

I am somewhat new to this stuff, but can you guide me on making a macr
to do this, or will I have to do it in VBA.

Thanks-- Jack

 

[Bernie Deitrick]

Jack,

Copy the code below and paste it into a codemodule of an otherwise empty
workbook.

Run the first macro, and select the files you want.

HTH,
Bernie
MS Excel MVP

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
Dim myFile As Variant

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*", _
MultiSelect:=True)
If FName(1) = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")

For Each myFile In FName
ImportTextFile CStr(myFile), Sep
Next

End Sub

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = 1
RowNdx = Range("A65536").End(xlUp)(2).Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

 

[jtp550]

Thanks-- It works great. I use to do alot of VB6 programming, bu
haven't for a couple of years. It's suprising how rusty one gets afte
a long layoff like this.

Excel is a great application, and with people like you around,it wil
be for years ahead. Again many thanks

Jac