extract data from txt file

[youngtreeest]

Hello guys!

I have a simple question that I just can't solve.

I have a txt file and I want to extract more than one latitude and longitude information and put them into a spreadsheet:

Some information here..
1990
latitude: 72n31
longitude: 15w27
1991
latitude: 71n30
longitude: 14w26
Some more information here..

The vba code I have is:

Private Sub CommandButton1_Click()

Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "C:\test\geographical-coordinates.txt"
'myFile = Application.GetOpenFilename()

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1

posLat = InStr(text, "latitude")
posLong = InStr(text, "longitude")

Range("A1").Value = Mid(text, posLat + 10, 5)
Range("A2").Value = Mid(text, posLong + 11, 5)

End Sub

My question is that how should I design a loop structure that can fetch all the data and put them into different cells?

Thank you very much!

[westconn1]

try like

rw = 1 ' first row for data
posLat = 1
posLong =1
do
posLat = InStr(poslat + 2, text, "latitude")
posLong = InStr(poslong + 2, text, "longitude")
if poslat = 0 then exit do ' exit loop when no more latitude
cells(rw, 1) = Mid(text, posLat + 10, 5)
cells(rw, 2) = Mid(text, posLong + 11, 5)
rw = rw + 1
loopchange columns to increment if that is preferred

[youngtreeest]

Thank you so much westconn1! Really appreciate!!

I did like what you said. It is executable but nothing transfer to the spreadsheet. Could you please take another look? Thank you so much!


Private Sub CommandButton1_Click()

Dim myFile As String, text As String, posLat As Integer, posLong As Integer, rw As Integer, cl As Integer

myFile = "C:\Users\Desktop\New folder\geographical-coordinates.txt"

rw = 1 ' first row for data
cl = 1 ' first column for data
posLat = 1
posLong = 1

Do
posLat = InStr(posLat + 2, text, "latitude") ' does 2 mean two entries in the whole file"
posLong = InStr(posLong + 2, text, "longitude")
If posLat = 0 Then Exit Do 'exit loop when no more latitude
Cells(rw, cl) = Mid(text, posLat + 10, 5)
Cells(rw, cl + 1) = Mid(text, posLong + 11, 5)
rw = rw + 1
cl = cl + 1

Loop

Close #1

End Sub

[snb]

Please use code tags !!

why not:


Sub M_snb()
Workbooks.Open "C:\Users\Desktop\New folder\geographical-coordinates.txt"
End Sub

[youngtreeest]

sorry....I will use it next time.

[westconn1]

"C:\Users\Desktop\New folder\geographical-coordinates.txt"i would doubt that is a valid file path, no user name

[youngtreeest]

i would doubt that is a valid file path, no user name

Thanks for your time.

I intentionally deleted my name when I posted it on the forum. Double check the path is correct.

Could be anything else?

[westconn1]

Could be anything else?without testing hard to guess

though maybe i missed that you had not read the file into the text variable
i only posted the code to add, not the complete procedure, parts that were already working

you still needed

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1before the part i posted

[youngtreeest]

without testing hard to guess

though maybe i missed that you had not read the file into the text variable
i only posted the code to add, not the complete procedure, parts that were already working

you still needed

Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1before the part i posted

Gosh problem solved!

You are such a life saver!

Again thank you SO much!!!

[snb]

Don't be satisfied too early


Open myFile For Input As #1
text=Input(LOF(1),1)
Close

or again:


Sub M_snb()
Workbooks.Open "C:\Users\Desktop\New folder\geographical-coordinates.txt"
End Sub