-
I wrote the Split Line 2 function and tried to run it with the code below. It put the remaining data in one cell.
[VBA] Sub AustenSplitFile()
Dim vFile As String, vFF As Long, tempStr As String
Dim FileCont() As String, Cnt As Long, i As Long
vFile = Application.GetOpenFilename("Text Files,*.txt,All Files,*.*")
If LCase(vFile) = "false" Then Exit Sub
vFF = FreeFile
Open vFile For Input As #vFF
Do Until EOF(vFF)
Line Input #vFF, tempStr
ReDim Preserve FileCont(Cnt + 1)
FileCont(Cnt) = Left$(tempStr, 3752)
FileCont(Cnt + 1) = Mid$(tempStr, 3753)
Cnt = Cnt + 2
Loop
Close #vFF
Application.ScreenUpdating = False
If ActiveWorkbook Is Nothing Then
Workbooks.Add -4167
Else
Sheets.Add After:=Sheets(Sheets.Count)
End If
For i = 0 To Cnt - 2 Step 2
Cells(i + 1, 1).Resize(1, 256) = SplitLine1(FileCont(i))
Cells(i + 2, 1).Resize(1, 21) = SplitLine2(FileCont(i + 1))
Cells(i + 2, 1) = FileCont(i + 1)
Next
Application.ScreenUpdating = False
End Sub
Function SplitLine1(ByVal tempStr As String) As String()
Dim tempArr() As String
tempStr = Replace(Replace(tempStr, Chr(255), ""), Chr(254), "")
ReDim tempArr(1 To 1, 1 To 256)
tempArr(1, 1) = Mid$(tempStr, 1, 1)
tempArr(1, 2) = Mid$(tempStr, 2, 2)
tempArr(1, 3) = Mid$(tempStr, 4, 12)
tempArr(1, 4) = Mid$(tempStr, 16, 35)
tempArr(1, 5) = Mid$(tempStr, 51, 25)
tempArr(1, 6) = Mid$(tempStr, 76, 25)
tempArr(1, 7) = Mid$(tempStr, 101, 10)
tempArr(1, 8) = Mid$(tempStr, 112, 4)
tempArr(1, 9) = Mid$(tempStr, 112, 4)
tempArr(1, 10) = Mid$(tempStr, 117, 1)
tempArr(1, 11) = Mid$(tempStr, 118, 11)
tempArr(1, 14) = Mid$(tempStr, 130, 11)
tempArr(1, 16) = Mid$(tempStr, 142, 11)
tempArr(1, 18) = Mid$(tempStr, 154, 11)
tempArr(1, 20) = Mid$(tempStr, 166, 11)
tempArr(1, 21) = Mid$(tempStr, 178, 11)
tempArr(1, 22) = Mid$(tempStr, 190, 11)
tempArr(1, 23) = Mid$(tempStr, 202, 11)
tempArr(1, 24) = Mid$(tempStr, 214, 3)
tempArr(1, 25) = Mid$(tempStr, 218, 3)
tempArr(1, 26) = Mid$(tempStr, 222, 3)
tempArr(1, 27) = Mid$(tempStr, 226, 3)
tempArr(1, 28) = Mid$(tempStr, 230, 3)
tempArr(1, 29) = Mid$(tempStr, 234, 3)
tempArr(1, 30) = Mid$(tempStr, 238, 3)
tempArr(1, 31) = Mid$(tempStr, 242, 3)
tempArr(1, 32) = Mid$(tempStr, 246, 11)
tempArr(1, 33) = Mid$(tempStr, 257, 10)
tempArr(1, 34) = Mid$(tempStr, 267, 10)
tempArr(1, 35) = Mid$(tempStr, 277, 11)
tempArr(1, 36) = Mid$(tempStr, 288, 2)
tempArr(1, 37) = Mid$(tempStr, 290, 12)
tempArr(1, 38) = Mid$(tempStr, 302, 12)
tempArr(1, 39) = Mid$(tempStr, 314, 25)
tempArr(1, 40) = Mid$(tempStr, 339, 2)
tempArr(1, 41) = Mid$(tempStr, 341, 12)
tempArr(1, 42) = Mid$(tempStr, 353, 8)
tempArr(1, 43) = Mid$(tempStr, 362, 15)
tempArr(1, 44) = Mid$(tempStr, 377, 5)
tempArr(1, 45) = Mid$(tempStr, 382, 5)
tempArr(1, 46) = Mid$(tempStr, 387, 1)
tempArr(1, 47) = Mid$(tempStr, 388, 1)
tempArr(1, 48) = Mid$(tempStr, 389, 25)
tempArr(1, 49) = Mid$(tempStr, 414, 10)
tempArr(1, 50) = Mid$(tempStr, 424, 12)
tempArr(1, 51) = Mid$(tempStr, 436, 10)
tempArr(1, 52) = Mid$(tempStr, 446, 26)
tempArr(1, 53) = Mid$(tempStr, 472, 3)
tempArr(1, 54) = Mid$(tempStr, 476, 11)
tempArr(1, 55) = Mid$(tempStr, 487, 1)
tempArr(1, 56) = Mid$(tempStr, 488, 3)
tempArr(1, 57) = Mid$(tempStr, 491, 1)
tempArr(1, 58) = Mid$(tempStr, 492, 1)
tempArr(1, 59) = Mid$(tempStr, 493, 2)
tempArr(1, 60) = Mid$(tempStr, 495, 1)
tempArr(1, 61) = Mid$(tempStr, 497, 1)
tempArr(1, 62) = Mid$(tempStr, 499, 1)
tempArr(1, 63) = Mid$(tempStr, 501, 1)
tempArr(1, 64) = Mid$(tempStr, 503, 1)
tempArr(1, 65) = Mid$(tempStr, 504, 1)
tempArr(1, 66) = Mid$(tempStr, 507, 2)
tempArr(1, 67) = Mid$(tempStr, 509, 1)
tempArr(1, 68) = Mid$(tempStr, 511, 1)
tempArr(1, 69) = Mid$(tempStr, 513, 1)
tempArr(1, 70) = Mid$(tempStr, 515, 12)
tempArr(1, 71) = Mid$(tempStr, 528, 12)
tempArr(1, 72) = Mid$(tempStr, 541, 12)
tempArr(1, 73) = Mid$(tempStr, 554, 12)
tempArr(1, 74) = Mid$(tempStr, 567, 12)
tempArr(1, 75) = Mid$(tempStr, 580, 2)
tempArr(1, 76) = Mid$(tempStr, 583, 3)
tempArr(1, 77) = Mid$(tempStr, 587, 1)
tempArr(1, 78) = Mid$(tempStr, 589, 1)
tempArr(1, 79) = Mid$(tempStr, 591, 1)
tempArr(1, 80) = Mid$(tempStr, 593, 1)
tempArr(1, 81) = Mid$(tempStr, 595, 1)
tempArr(1, 82) = Mid$(tempStr, 597, 1)
tempArr(1, 83) = Mid$(tempStr, 599, 1)
tempArr(1, 84) = Mid$(tempStr, 601, 1)
tempArr(1, 85) = Mid$(tempStr, 603, 1)
tempArr(1, 86) = Mid$(tempStr, 605, 2)
tempArr(1, 87) = Mid$(tempStr, 608, 50)
tempArr(1, 88) = Mid$(tempStr, 659, 10)
tempArr(1, 89) = Mid$(tempStr, 670, 10)
tempArr(1, 90) = Mid$(tempStr, 681, 17)
tempArr(1, 91) = Mid$(tempStr, 699, 50)
tempArr(1, 92) = Mid$(tempStr, 750, 55)
tempArr(1, 93) = Mid$(tempStr, 806, 55)
tempArr(1, 94) = Mid$(tempStr, 862, 55)
tempArr(1, 95) = Mid$(tempStr, 918, 30)
tempArr(1, 96) = Mid$(tempStr, 949, 2)
tempArr(1, 97) = Mid$(tempStr, 952, 15)
tempArr(1, 98) = Mid$(tempStr, 968, 16)
tempArr(1, 99) = Mid$(tempStr, 985, 35)
tempArr(1, 100) = Mid$(tempStr, 1021, 25)
tempArr(1, 101) = Mid$(tempStr, 1047, 25)
tempArr(1, 102) = Mid$(tempStr, 1073, 50)
tempArr(1, 103) = Mid$(tempStr, 1124, 10)
tempArr(1, 104) = Mid$(tempStr, 1135, 10)
tempArr(1, 105) = Mid$(tempStr, 1148, 17)
tempArr(1, 106) = Mid$(tempStr, 1164, 50)
tempArr(1, 107) = Mid$(tempStr, 1215, 55)
tempArr(1, 108) = Mid$(tempStr, 1271, 55)
tempArr(1, 109) = Mid$(tempStr, 1327, 55)
tempArr(1, 110) = Mid$(tempStr, 1383, 30)
tempArr(1, 111) = Mid$(tempStr, 1414, 2)
tempArr(1, 112) = Mid$(tempStr, 1417, 15)
tempArr(1, 113) = Mid$(tempStr, 1433, 16)
tempArr(1, 114) = Mid$(tempStr, 1450, 25)
tempArr(1, 115) = Mid$(tempStr, 1476, 2)
tempArr(1, 116) = Mid$(tempStr, 1479, 35)
tempArr(1, 117) = Mid$(tempStr, 1515, 25)
tempArr(1, 118) = Mid$(tempStr, 1541, 25)
tempArr(1, 119) = Mid$(tempStr, 1567, 55)
tempArr(1, 120) = Mid$(tempStr, 1623, 55)
tempArr(1, 121) = Mid$(tempStr, 1679, 55)
tempArr(1, 122) = Mid$(tempStr, 1735, 30)
tempArr(1, 123) = Mid$(tempStr, 1766, 2)
tempArr(1, 124) = Mid$(tempStr, 1769, 15)
tempArr(1, 125) = Mid$(tempStr, 1785, 3)
tempArr(1, 126) = Mid$(tempStr, 1789, 3)
tempArr(1, 127) = Mid$(tempStr, 1793, 80)
tempArr(1, 128) = Mid$(tempStr, 1874, 14)
tempArr(1, 129) = Mid$(tempStr, 1889, 14)
tempArr(1, 130) = Mid$(tempStr, 1904, 10)
tempArr(1, 131) = Mid$(tempStr, 1915, 1)
tempArr(1, 132) = Mid$(tempStr, 1916, 80)
tempArr(1, 133) = Mid$(tempStr, 1997, 2)
tempArr(1, 134) = Mid$(tempStr, 2000, 50)
tempArr(1, 135) = Mid$(tempStr, 2051, 25)
tempArr(1, 136) = Mid$(tempStr, 2077, 25)
tempArr(1, 137) = Mid$(tempStr, 2103, 55)
tempArr(1, 138) = Mid$(tempStr, 2159, 55)
tempArr(1, 139) = Mid$(tempStr, 2215, 55)
tempArr(1, 140) = Mid$(tempStr, 2271, 30)
tempArr(1, 141) = Mid$(tempStr, 2302, 2)
tempArr(1, 142) = Mid$(tempStr, 2305, 15)
tempArr(1, 143) = Mid$(tempStr, 2321, 3)
tempArr(1, 144) = Mid$(tempStr, 2329, 81)
tempArr(1, 145) = Mid$(tempStr, 2325, 3)
tempArr(1, 146) = Mid$(tempStr, 2329, 80)
tempArr(1, 147) = Mid$(tempStr, 2410, 14)
tempArr(1, 148) = Mid$(tempStr, 2424, 10)
tempArr(1, 149) = Mid$(tempStr, 2436, 11)
tempArr(1, 150) = Mid$(tempStr, 2448, 3)
tempArr(1, 151) = Mid$(tempStr, 2452, 11)
tempArr(1, 151) = Mid$(tempStr, 2464, 3)
tempArr(1, 152) = Mid$(tempStr, 2468, 11)
tempArr(1, 153) = Mid$(tempStr, 2480, 3)
tempArr(1, 154) = Mid$(tempStr, 2484, 11)
tempArr(1, 155) = Mid$(tempStr, 2496, 3)
tempArr(1, 156) = Mid$(tempStr, 2500, 8)
tempArr(1, 157) = Mid$(tempStr, 2509, 1)
tempArr(1, 158) = Mid$(tempStr, 2511, 4)
tempArr(1, 159) = Mid$(tempStr, 2516, 8)
tempArr(1, 160) = Mid$(tempStr, 2525, 11)
tempArr(1, 161) = Mid$(tempStr, 2537, 9)
tempArr(1, 162) = Mid$(tempStr, 2547, 3)
tempArr(1, 163) = Mid$(tempStr, 2551, 1)
tempArr(1, 164) = Mid$(tempStr, 2553, 1)
tempArr(1, 165) = Mid$(tempStr, 2555, 2)
tempArr(1, 166) = Mid$(tempStr, 2558, 2)
tempArr(1, 167) = Mid$(tempStr, 2561, 1)
tempArr(1, 168) = Mid$(tempStr, 2563, 13)
tempArr(1, 169) = Mid$(tempStr, 2577, 50)
tempArr(1, 170) = Mid$(tempStr, 2628, 55)
tempArr(1, 171) = Mid$(tempStr, 2684, 55)
tempArr(1, 172) = Mid$(tempStr, 2740, 55)
tempArr(1, 173) = Mid$(tempStr, 2796, 30)
tempArr(1, 174) = Mid$(tempStr, 2827, 2)
tempArr(1, 175) = Mid$(tempStr, 2830, 15)
tempArr(1, 176) = Mid$(tempStr, 2846, 4)
tempArr(1, 177) = Mid$(tempStr, 2851, 5)
tempArr(1, 178) = Mid$(tempStr, 2857, 15)
tempArr(1, 179) = Mid$(tempStr, 2873, 15)
tempArr(1, 180) = Mid$(tempStr, 2889, 5)
tempArr(1, 181) = Mid$(tempStr, 2895, 1)
tempArr(1, 182) = Mid$(tempStr, 2897, 1)
tempArr(1, 183) = Mid$(tempStr, 2899, 1)
tempArr(1, 184) = Mid$(tempStr, 2901, 11)
tempArr(1, 185) = Mid$(tempStr, 2913, 1)
tempArr(1, 186) = Mid$(tempStr, 2915, 11)
tempArr(1, 187) = Mid$(tempStr, 2927, 11)
tempArr(1, 188) = Mid$(tempStr, 2939, 11)
tempArr(1, 189) = Mid$(tempStr, 2951, 1)
tempArr(1, 190) = Mid$(tempStr, 2953, 11)
tempArr(1, 191) = Mid$(tempStr, 2965, 1)
tempArr(1, 192) = Mid$(tempStr, 2966, 1)
tempArr(1, 193) = Mid$(tempStr, 2968, 1)
tempArr(1, 194) = Mid$(tempStr, 2970, 1)
tempArr(1, 195) = Mid$(tempStr, 2972, 1)
tempArr(1, 196) = Mid$(tempStr, 2974, 1)
tempArr(1, 197) = Mid$(tempStr, 2976, 1)
tempArr(1, 198) = Mid$(tempStr, 2978, 1)
tempArr(1, 199) = Mid$(tempStr, 2980, 1)
tempArr(1, 200) = Mid$(tempStr, 2982, 1)
tempArr(1, 201) = Mid$(tempStr, 2984, 1)
tempArr(1, 202) = Mid$(tempStr, 2986, 1)
tempArr(1, 203) = Mid$(tempStr, 2989, 2)
tempArr(1, 204) = Mid$(tempStr, 2991, 2)
tempArr(1, 205) = Mid$(tempStr, 2993, 2)
tempArr(1, 206) = Mid$(tempStr, 2995, 2)
tempArr(1, 207) = Mid$(tempStr, 2997, 2)
tempArr(1, 208) = Mid$(tempStr, 2999, 12)
tempArr(1, 209) = Mid$(tempStr, 3012, 12)
tempArr(1, 210) = Mid$(tempStr, 3025, 1)
tempArr(1, 211) = Mid$(tempStr, 3027, 2)
tempArr(1, 212) = Mid$(tempStr, 3030, 12)
tempArr(1, 213) = Mid$(tempStr, 3042, 3)
tempArr(1, 214) = Mid$(tempStr, 3046, 29)
tempArr(1, 215) = Mid$(tempStr, 3076, 25)
tempArr(1, 216) = Mid$(tempStr, 3102, 25)
tempArr(1, 217) = Mid$(tempStr, 3128, 50)
tempArr(1, 218) = Mid$(tempStr, 3179, 17)
tempArr(1, 219) = Mid$(tempStr, 3197, 1)
tempArr(1, 220) = Mid$(tempStr, 3199, 11)
tempArr(1, 221) = Mid$(tempStr, 3211, 11)
tempArr(1, 222) = Mid$(tempStr, 3223, 10)
tempArr(1, 223) = Mid$(tempStr, 3233, 1)
tempArr(1, 224) = Mid$(tempStr, 3234, 1)
tempArr(1, 225) = Mid$(tempStr, 3235, 17)
tempArr(1, 226) = Mid$(tempStr, 3253, 11)
tempArr(1, 227) = Mid$(tempStr, 3265, 11)
tempArr(1, 228) = Mid$(tempStr, 3277, 1)
tempArr(1, 229) = Mid$(tempStr, 3279, 2)
tempArr(1, 230) = Mid$(tempStr, 3282, 17)
tempArr(1, 231) = Mid$(tempStr, 3300, 11)
tempArr(1, 232) = Mid$(tempStr, 3312, 10)
tempArr(1, 233) = Mid$(tempStr, 3323, 10)
tempArr(1, 234) = Mid$(tempStr, 3334, 55)
tempArr(1, 235) = Mid$(tempStr, 3390, 55)
tempArr(1, 236) = Mid$(tempStr, 3446, 55)
tempArr(1, 237) = Mid$(tempStr, 3502, 30)
tempArr(1, 238) = Mid$(tempStr, 3533, 2)
tempArr(1, 239) = Mid$(tempStr, 3536, 15)
tempArr(1, 240) = Mid$(tempStr, 3552, 3)
tempArr(1, 241) = Mid$(tempStr, 3556, 3)
tempArr(1, 242) = Mid$(tempStr, 3560, 3)
tempArr(1, 243) = Mid$(tempStr, 3564, 12)
tempArr(1, 244) = Mid$(tempStr, 3577, 17)
tempArr(1, 245) = Mid$(tempStr, 3595, 11)
tempArr(1, 246) = Mid$(tempStr, 3607, 3)
tempArr(1, 247) = Mid$(tempStr, 3577, 18)
tempArr(1, 248) = Mid$(tempStr, 3611, 1)
tempArr(1, 249) = Mid$(tempStr, 3613, 1)
tempArr(1, 250) = Mid$(tempStr, 3615, 11)
tempArr(1, 251) = Mid$(tempStr, 3627, 1)
tempArr(1, 252) = Mid$(tempStr, 3629, 1)
tempArr(1, 253) = Mid$(tempStr, 3631, 15)
SplitLine1 = tempArr
End Function
Function SplitLine2(ByVal tempStr As String) As String()
Dim tempArr() As String
tempStr = Replace(Replace(tempStr, Chr(255), ""), Chr(254), "")
ReDim tempArr(1 To 1, 1 To 21)
tempArr(1, 1) = Mid$(tempStr, 3647, 13)
tempArr(1, 2) = Mid$(tempStr, 3661, 12)
tempArr(1, 3) = Mid$(tempStr, 3674, 1)
tempArr(1, 4) = Mid$(tempStr, 3676, 15)
tempArr(1, 5) = Mid$(tempStr, 3692, 11)
tempArr(1, 6) = Mid$(tempStr, 3704, 11)
tempArr(1, 7) = Mid$(tempStr, 3716, 11)
tempArr(1, 8) = Mid$(tempStr, 3728, 11)
tempArr(1, 9) = Mid$(tempStr, 3740, 11)
tempArr(1, 10) = Mid$(tempStr, 3752, 20)
tempArr(1, 11) = Mid$(tempStr, 3773, 12)
tempArr(1, 12) = Mid$(tempStr, 3786, 5)
tempArr(1, 13) = Mid$(tempStr, 3792, 3)
tempArr(1, 14) = Mid$(tempStr, 3796, 1)
tempArr(1, 15) = Mid$(tempStr, 3798, 11)
tempArr(1, 16) = Mid$(tempStr, 3810, 14)
tempArr(1, 17) = Mid$(tempStr, 3825, 14)
tempArr(1, 18) = Mid$(tempStr, 3840, 1)
tempArr(1, 19) = Mid$(tempStr, 3842, 5)
tempArr(1, 20) = Mid$(tempStr, 3848, 2)
tempArr(1, 21) = Mid$(tempStr, 3851, 5)
SplitLine2 = tempArr
End Function
[/VBA]
Any ideas?
-
Remove this line:[vba]Cells(i + 2, 1) = FileCont(i + 1)[/vba]Directly below where you're caling SplitLine2, should take care of it
-
Hi Matt,
Now row 2 is blank with the above line removed.
-
Hi Austen,
You're right, it is. Keep that line out, I just looked at your SplitLine2 function a little closer.
The string being sent to it is not the 3856 character line, it is the line from character 3753 and on. So instead of using something like[vba] tempArr(1, 1) = Mid$(tempStr, 3647, 13)
tempArr(1, 2) = Mid$(tempStr, 3661, 12)[/vba]Instead use:[vba] tempArr(1, 1) = Mid$(tempStr, 1, 13)
tempArr(1, 2) = Mid$(tempStr, 14, 12)
'etc[/vba]I didn't look closely enough yesterday, sorry about that.
Matt