M
MaryLindholm
I have a text file I am moving into excel. Some rows start with 02 and
some with 03. What I want to happen is that excel will write an 02 row
into excel, then when it sees an 03 row it will put it at the end of
the 02 row above it.
For some reason the 03 rows aren't being written onto the spread sheet,
but the 02 rows are fine. I would be happy if the 03 row just began at
the end of the 02 even if it were on the line down from it.
So say row 1 ends at AA then row 2 will start at AB
Here is what I have:
Sub VariedLineImport()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = Application.GetOpenFilename
If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add Template:=xlWorksheet
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
'Etc
If Left(ResultStr, 2) = "02" Then
'do the splitting here, like
Cells(Counter, 1).Value = Mid(ResultStr, 3, 9)
Cells(Counter, 2).Value = Mid(ResultStr, 12, 20)
Cells(Counter, 3).Value = Mid(ResultStr, 32, 5)
Cells(Counter, 4).Value = Mid(ResultStr, 37, 15)
Cells(Counter, 5).Value = Mid(ResultStr, 52, 15)
Cells(Counter, 6).Value = Mid(ResultStr, 67, 25)
Cells(Counter, 7).Value = Mid(ResultStr, 92, 40)
Cells(Counter, 8).Value = Mid(ResultStr, 132, 40)
Cells(Counter, 9).Value = Mid(ResultStr, 172, 40)
Cells(Counter, 10).Value = Mid(ResultStr, 212, 30)
Cells(Counter, 11).Value = Mid(ResultStr, 242, 5)
Cells(Counter, 12).Value = Mid(ResultStr, 247, 11)
Cells(Counter, 13).Value = Mid(ResultStr, 258, 2)
Cells(Counter, 14).Value = Mid(ResultStr, 260, 5)
Cells(Counter, 15).Value = Mid(ResultStr, 265, 10)
Cells(Counter, 16).Value = Mid(ResultStr, 275, 1)
Cells(Counter, 17).Value = Mid(ResultStr, 276, 3)
Cells(Counter, 18).Value = Mid(ResultStr, 279, 40)
Cells(Counter, 19).Value = Mid(ResultStr, 319, 10)
Cells(Counter, 20).Value = Mid(ResultStr, 329, 10)
Cells(Counter, 21).Value = Mid(ResultStr, 339, 3)
Cells(Counter, 22).Value = Mid(ResultStr, 364, 9)
Cells(Counter, 23).Value = Mid(ResultStr, 373, 10)
'Etc
ElseIf Left(ResultStr, 2) = "03" Then
'do the splitting here, like
Cells(Counter, 24).Value = Mid(ResultStr, 383, 9)
Cells(Counter, 25).Value = Mid(ResultStr, 392, 20)
Cells(Counter, 26).Value = Mid(ResultStr, 396, 4)
Cells(Counter, 27).Value = Mid(ResultStr, 400, 8)
Cells(Counter, 28).Value = Mid(ResultStr, 408, 4)
Cells(Counter, 29).Value = Mid(ResultStr, 412, 3)
Cells(Counter, 30).Value = Mid(ResultStr, 414, 2)
Cells(Counter, 31).Value = Mid(ResultStr, 424, 10)
Cells(Counter, 32).Value = Mid(ResultStr, 434, 10)
Cells(Counter, 33).Value = Mid(ResultStr, 444, 8)
Cells(Counter, 34).Value = Mid(ResultStr, 452, 3)
'Etc
'And so on for different starting values
End If
'Increment the Counter By 1
Counter = Counter + 1
Loop
'Close The Open Text File
Close
End Sub
Others have helped me get this far and it has been much appreciated.
some with 03. What I want to happen is that excel will write an 02 row
into excel, then when it sees an 03 row it will put it at the end of
the 02 row above it.
For some reason the 03 rows aren't being written onto the spread sheet,
but the 02 rows are fine. I would be happy if the 03 row just began at
the end of the 02 even if it were on the line down from it.
So say row 1 ends at AA then row 2 will start at AB
Here is what I have:
Sub VariedLineImport()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
FileName = Application.GetOpenFilename
If FileName = "" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Application.ScreenUpdating = False
Workbooks.Add Template:=xlWorksheet
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
'Etc
If Left(ResultStr, 2) = "02" Then
'do the splitting here, like
Cells(Counter, 1).Value = Mid(ResultStr, 3, 9)
Cells(Counter, 2).Value = Mid(ResultStr, 12, 20)
Cells(Counter, 3).Value = Mid(ResultStr, 32, 5)
Cells(Counter, 4).Value = Mid(ResultStr, 37, 15)
Cells(Counter, 5).Value = Mid(ResultStr, 52, 15)
Cells(Counter, 6).Value = Mid(ResultStr, 67, 25)
Cells(Counter, 7).Value = Mid(ResultStr, 92, 40)
Cells(Counter, 8).Value = Mid(ResultStr, 132, 40)
Cells(Counter, 9).Value = Mid(ResultStr, 172, 40)
Cells(Counter, 10).Value = Mid(ResultStr, 212, 30)
Cells(Counter, 11).Value = Mid(ResultStr, 242, 5)
Cells(Counter, 12).Value = Mid(ResultStr, 247, 11)
Cells(Counter, 13).Value = Mid(ResultStr, 258, 2)
Cells(Counter, 14).Value = Mid(ResultStr, 260, 5)
Cells(Counter, 15).Value = Mid(ResultStr, 265, 10)
Cells(Counter, 16).Value = Mid(ResultStr, 275, 1)
Cells(Counter, 17).Value = Mid(ResultStr, 276, 3)
Cells(Counter, 18).Value = Mid(ResultStr, 279, 40)
Cells(Counter, 19).Value = Mid(ResultStr, 319, 10)
Cells(Counter, 20).Value = Mid(ResultStr, 329, 10)
Cells(Counter, 21).Value = Mid(ResultStr, 339, 3)
Cells(Counter, 22).Value = Mid(ResultStr, 364, 9)
Cells(Counter, 23).Value = Mid(ResultStr, 373, 10)
'Etc
ElseIf Left(ResultStr, 2) = "03" Then
'do the splitting here, like
Cells(Counter, 24).Value = Mid(ResultStr, 383, 9)
Cells(Counter, 25).Value = Mid(ResultStr, 392, 20)
Cells(Counter, 26).Value = Mid(ResultStr, 396, 4)
Cells(Counter, 27).Value = Mid(ResultStr, 400, 8)
Cells(Counter, 28).Value = Mid(ResultStr, 408, 4)
Cells(Counter, 29).Value = Mid(ResultStr, 412, 3)
Cells(Counter, 30).Value = Mid(ResultStr, 414, 2)
Cells(Counter, 31).Value = Mid(ResultStr, 424, 10)
Cells(Counter, 32).Value = Mid(ResultStr, 434, 10)
Cells(Counter, 33).Value = Mid(ResultStr, 444, 8)
Cells(Counter, 34).Value = Mid(ResultStr, 452, 3)
'Etc
'And so on for different starting values
End If
'Increment the Counter By 1
Counter = Counter + 1
Loop
'Close The Open Text File
Close
End Sub
Others have helped me get this far and it has been much appreciated.