I am trying to selectively read a text file into a spreadsheet. Up until now I just copy and paste it in. This is fairly easy, but I'd like to be able to just load the parts of the file I want and have it load only the parts I want. The text file has a lot of extraneous information (180,000 lines) and I need less than 100. I have written some code to read the file and capture only the lines I want. Having done that, I want to transfer parts of the captured data into a range in a spreadsheet. Here's the code I have so far:
Sub ReadLSTFile()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
Dim i As Integer
Dim LOC As String
Dim Unit As String
Dim PERIOD As String
Dim LineParsed As Variant
Dim ColName
Dim MyData As Variant
Dim FindColumns As Integer
myFile = ActiveSheet.Range("LSTFileName")
LOC = Range("LOC") + Range("LOC")
Unit = Range("Unit")
PERIOD = Range("PERIOD")
LOC = LOC + PERIOD + Unit
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, text
If InStr(1, text, "Columns Section") > 0 Then FindColumns = FindColumns + 1
If Mid(text, 2, 1) = "C" And FindColumns = 2 And InStr(text, LOC) > 0 Then
LineParsed = SplitEx(text, " ", Chr(34), True)
If LineParsed(5) <> 0 Then Debug.Print text
End If
Loop
Close #1
End Sub
This is what the print statement produces:
C 460 CCISRULnA BS 26.859595 0 -0
C 462 CCISRUlhC BS 82.012188 0 0
C 463 CCISRU..0 BS 121.888497 0 -0
C 464 CCISRULTD BS 26.859595 0 0
C 466 CCISRUhtD BS 82.012188 0 0
C 467 CCISRUL9D BS 25.031633 0 -0
C 472 CCISRUh9D BS 62.273607 0 0
C 475 CCISRUSP1 BS 5.536804 0 -0
C 476 CCISRUSA1 BS 0.749063 0 0
C 477 CCISRUSI1 BS 1.874372 0 0
C 478 CCISRUSN1 BS 0.713030 0 0
C 479 CCISRUSC1 BS 0.111416 0 0
C 480 CCISRUSH1 BS 1.072207 0 0
C 481 CCISRUSB1 BS 0.275186 0 0
C 484 CCISRUhP1 BS 12.941670 0 -0
C 485 CCISRUhA1 BS 1.521523 0 0
C 486 CCISRUd11 BS 180.620594 0 0
C 488 CCISRUd31 BS 73.606563 0 0
C 490 CCISRUh8D BS 15.131447 0 0
C 491 CCISRUDMY BS 91.600996 0 0
C 492 CCISRUHtM BS 189.015874 0 0
C 493 CCISRUnnA BS 13.016714 0 0
C 495 CCISRUh2C BS 13.016714 0 0
C 497 CCISRUhaa BS 5.533621 0 0
C 498 CCISRUhbb BS 16.896174 0 0
C 499 CCISRUHMa BS 2.681707 0 0
C 500 CCISRUHmb BS 16.896174 0 0
C 501 CCISRU85R BS 13.920931 0 0
C 502 CCISRU90R BS 89.085954 0 0
So far, so good. I just want the 3rd and 5th columns of data to go on the spreadsheet. I know I can get this done by brute force, but it seems like there must be a simple way to transfer the data to an array and plug it into the spreadsheet, and I'd like to know what that might be without have to reinvent the wheel.
Thanks.
Bookmarks