Importing certain text from a .txt file

[gr8_big_geek]

Greetings Friends!

My am using Excel 2003~

I am once again working on a project and have hit an absolute wall! Here is what I'm trying to do:

1. Create a Userform that has one textbox and one button.
2. Have the user enter text in the textbox and press enter.
3. Once enter is pressed have the form take the data they typed into
the textbox and search a specific .txt file (beginning the search from
bottom to top to find the most recent entry) until it finds a match.
4. Take the latitude and longitude to the right of the match and convert
them to proper form. Right now my SQL provides live updates to the .txt
file only the lat/lon come accross as |-112053440|33427640|, so I need
to insert decimals in the correct places.
5. Either way, they type "c123" it will search until it matches that then
take the latitude and longitude that is to the right of the match.
6. Convert the latitude/longitude into proper format because if it pulls the
data as is it will appear as:

http://maps.google.com/maps?q=<wbr>+33480260,-11174307

which will return with an error, unless it has those decimals in there like this:

http://maps.google.com/maps?q=<wbr>+33.480260,-111.74307

If I can get the information to be imported and converted into string format I need to open a new instance of IE and have it open to this webpage.

The text in the .txt document looks like this:

[Sat Apr 10 05:53:30 2010] |AVL1|C123|-112065870|33428710|0780|0780|064000|064000|||||46412000|0|2|1000|3|20||
[Sat Apr 10 05:53:31 2010] |AVL1|C123|-112065260|33428810|0800|0800|064000|064000|||||46414000|0|2|1000|3|20||
[Sat Apr 10 05:53:52 2010] |AVL1|C123|-112059120|33428540|1000|1000|064000|064000|||||46434000|0|2|1000|3|20||
[Sat Apr 10 05:54:11 2010] |AVL1|C123|-112053440|33427640|1020|1020|054000|054000|||||46454000|0|2|1000|3|20||

Before I was manually having to type the latitude and longitude in and then generating the webpage on an AVI using:

Option Explicit
Public Sub DMS_Lookup()
On Error Resume Next
Dim mylat As String
Dim mylong As String
Dim sendstring As String
mylong = Sheet1.Cells(3, 20).Text
mylat = Sheet1.Cells(2, 20).Text
sendstring = mylat & ", " & mylong
Sheet1.WebBrowser1.Navigate ("http://maps.google.com/maps?<wbr>q=" & mylat & ", " & mylong)
Sheet1.WebBrowser1.Height = 500
Sheet1.WebBrowser1.Width = 750
End Sub


But now I have my SQL set up to update this text file. Now I just need to get the file searched and the data to go straight to the webpage instead.

Any help you could provide on the VBA coding for this would be wonderful!

Thank you in advance,

Matt~

 

[BrianB]

This seems to do it. Hopefully the lat & long numbers are consistent in magnitude. Both consist of 8 characters.

'=============================================================================
'- LOOK UP A MAP REFERENCE IN A TEXT FILE (HERE PIPE DELIMITED)
'- GET THE GOOGLE MAPS WEB PAGE
'- USES A REGULAR EXPRESSION TO EXTRACT LINES OF TEXT CONTAINING LOOKUP
'- Brian Baulsom April 2010
'=============================================================================
Sub GET_MAP()
    Const MyFile As String = "F:\Test\Test.txt"
    Const GoogleMaps As String = "http://maps.google.com/maps?q="
    '-------------------------------------------------------------------------
    Dim MyLookup As String
    Dim MyLat As String
    Dim MyLong As String
    Dim MyURL As String
    '-
    Dim FileString As String    ' text file
    Dim MyRegExp As Object      ' RE object
    Dim MyPattern As String     ' matching pattern
    Dim MyMatches As Variant    ' set of matching text lines. Zero base array
    Dim MyLine As Variant       ' single line split into fields. Zero base array
    Dim IE As Object            ' Internet Explorer
    '-------------------------------------------------------------------------
    '- LOOKUP VALUE
    MyLookup = "C123"
    '-------------------------------------------------------------------------
    '- READ THE TEXT FILE INTO MEMORY AND CLOSE IT
    Open MyFile For Input As #1
        FileString = Input(FileLen(MyFile), #1)
    Close #1
    '------------------------------------------------------------------------
    '- REGULAR EXPRESSION
    MyPattern = ".*" & MyLookup & ".*"
    Set MyRegExp = CreateObject("VbScript.RegExp")
    With MyRegExp
        .Global = True
        .ignorecase = True
        .Pattern = MyPattern
        Set MyMatches = .Execute(FileString)
    End With
    '-----------------------------------------------------------------------
    '- CLEAR THE TEXT FILE
    FileString = ""
    '-----------------------------------------------------------------------
    '-  GET THE LAST MATCHING LINE & MAKE URL
    '-----------------------------------------------------------------------
    MyLine = Split(MyMatches(MyMatches.Count - 1), "|", -1, vbTextCompare)
    MyLat = "+" & Left(MyLine(4), 2) & "." & Right(MyLine(4), 6)
    MyLong = Left(MyLine(3), 4) & "." & Right(MyLine(3), 5)
    MyURL = GoogleMaps & MyLat & "," & MyLong
    '-----------------------------------------------------------------------
    '-  GET THE WEB PAGE
    '-----------------------------------------------------------------------
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate MyURL
    Do Until IE.readystate = 4
        DoEvents
    Loop
    '-------------------------------------------------------------------------
End Sub
'=============================================================================

 

[gr8_big_geek]

Thank you very much for all of your help on that! I will try your code out and see if I can't get it to work. I do enjoy VBA and I love programming with it. I also use Autoit! Not sure if you have heard of it before but I was able to get the code to work in Autoit as well. If you would be interested in seeing my code for that I will send that to you.

Autoit allows you to make stand alone programs (it can compile them into .exe files). So the one I wrote is its own stand alone lat/lon lookup program.

Thank you very much for your response!

Your friend,

Matt~

 

[BrianB]

Thanks for the information. I also use Visual Basic 6 (and now free Visual Studio 2008). The benefit is that that language is practically identical. It is quite amazing what is possible using Excel and VBA (from using pictures to mp3 file handling) I do not have to use the others much - except when I need a .exe file.