Record a macro when you open the text file. You'll see the data|text to columns
wizard to help separate the fields.
(keep recording...)
Then when you're done importing the data, click on A1 and hit ctrl-*
(ctrl-asterisk) to select the current region. Copy that and paste at the bottom
of the other worksheet. Close the text file. And stop recording.
You'll get code that's specific to your text file layout.
I got this as my recorded macro:
Option Explicit
Sub Macro1()
ChDir "C:\My Documents\excel"
Workbooks.OpenText Filename:="C:\My Documents\excel\log.txt", Origin:=437, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(10 _
, 1), Array(30, 1)), TrailingMinusNumbers:=True
Selection.CurrentRegion.Select
Selection.Copy
ActiveWindow.ActivateNext
Range("A7").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
ActiveWindow.Close
End Sub
But I could change it a bit to make it a little cleaner.
Sub testme01()
Dim myFileName As String
Dim ToWks As Worksheet
Dim destCell As Range
Dim txtWks As Worksheet
myFileName = "C:\my documents\excel\log.txt"
If Dir(myFileName) = "" Then
MsgBox "The input file is missing!"
Exit Sub
End If
Set ToWks = ActiveSheet 'run it from the final sheet
With ToWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Workbooks.OpenText Filename:=myFileName, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(30, 1))
Set txtWks = ActiveSheet
With txtWks
.Range("a1").CurrentRegion.Copy _
Destination:=destCell
.Parent.Close savechanges:=False
End With
End Sub
Try changing the name of the file and keep your version of the .opentext line
(nicely editted to look like the one above).
If you have trouble, post back with your code (not the workbook).
