Hi all,
Sorry for the title. My problem is the following. I have a txt file that i read into excel. The file looks like this..an excerpt:
01ABBBBBB
02Hello
0320110906 1JamesSmith
0520110905+004800000000+004865693590+0009996001601
.
.
.
The row that starts with 05 is the row that contain all the data. That row goes on for 10 000 entries then it starts again with the row 03 chaning to say for example 0320110906 1AdamJones
I can capture the data rows, ie 05 rows, no problems. But how can I loop the row 03 ie JamesSmith and put this beside the data rows 05 and then loop this until the next occurence of a 03 row and the loop the new 03 row beside the data row 05....etc..
This is what I have now:
Cheers
Rob
Sorry for the title. My problem is the following. I have a txt file that i read into excel. The file looks like this..an excerpt:
01ABBBBBB
02Hello
0320110906 1JamesSmith
0520110905+004800000000+004865693590+0009996001601
.
.
.
The row that starts with 05 is the row that contain all the data. That row goes on for 10 000 entries then it starts again with the row 03 chaning to say for example 0320110906 1AdamJones
I can capture the data rows, ie 05 rows, no problems. But how can I loop the row 03 ie JamesSmith and put this beside the data rows 05 and then loop this until the next occurence of a 03 row and the loop the new 03 row beside the data row 05....etc..
This is what I have now:
Code:
Sub TxtfileImport()
Dim objFso
Dim objTF
Dim i As Long
Dim X()
Dim ArrLines, aLine
Dim InFile As String
Dim z
With Application
.ScreenUpdating = False
.Application.EnableEvents = False
End With
InFile = "C:\temp\Data.txt"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objTF = objFso.OpenTextFile(InFile)
ArrLines = Split(objTF.ReadAll, vbCrLf)
ReDim X(1 To UBound(ArrLines, 1) + 1, 1 To 2)
For Each aLine In ArrLines
If Left$(aLine, 2) = "05" Then
i = i + 1
X(i, 1) = Mid$(aLine, 3, 10) 'gets date
X(i, 2) = Mid$(aLine, 37, 14) ' gets values
End If
Next
ThisWorkbook.Sheets("Sheet1").[a1].Resize(UBound(X, 1), UBound(X, 2)) = X
With Application
.ScreenUpdating = True
.Application.EnableEvents = True
End With
objTF.Close
Set objTF = Nothing
End SubRob
... I forgot to change the line to "03"....
