0

I am a bit old school and I need to be able to parse this document in VB6.

<ip>
<results>
<result>
<ip>104.145.4.78</ip>
<host>104.145.4.78</host>
<isp>Allstream Corp.</isp>
<city>Vancouver</city>
<countrycode>CA</countrycode>
<countryname>Canada</countryname>
<latitude>49.2551</latitude>
<longitude>-123.0667</longitude>
</result>
</results>
</ip>

There is a website that provides the location of and IP address's and i need to put the city name for about 6000 addresses and this is the best way I can think of except that I cannot find a simple way to get the data off of this page.

Here is the actual website I am using:

http://api.geoiplookup.net/?query=104.145.4.78

Any help here would be greatly appriciated. I have spent about 6 hours trying to figure this out and I am sure there is a simple solution but I can't figure out what it is.

Cheers,

Sumeet Bains
  • 1
  • 2
    Web scraping is a dubious activity at best. That site's Terms of Use say: "You may not copy content from this website without our prior permission." and "You may not use a script, agent, application or otherwise query this website in an automated fashion without prior written permission." Time to find another solution based on a public web service. – Bob77 Dec 23 '17 at 19:28
  • Have you looked up the MSXML library? – StayOnTarget Jan 02 '18 at 13:45

1 Answers1

-2

If you get each result individually, what you need is just only a basic helper function to tokenize your input data, like this one here:

' old school tokenizer
Public Function GetToken(ByVal xmlIn As String, ByVal tagName As String) As Variant
    Dim l as long, p1 As Long, p2 As Long
    l = Len(tagName)
    p1 = InStr(1, xmlIn, "<" + tagName + ">")
    p2 = InStr(p1, xmlIn, "</" + tagName + ">")
    GetToken = Mid(xmlIn, p1 + l + 2, p2 - p1 - l - 2)
End Function

...which you can use as follows:

' assume your data is in xml
Dim result As String, ip As String, city As String
result = GetToken(xml, "result")
ip = GetToken(result, "ip")
city = GetToken(result, "city")

Result:

Debug.Print ip, city => 104.145.4.78  Vancouver

Moreover, you can easily expand the tokenizer above to get multiple results into an array, if you want.

deblocker
  • 7,629
  • 2
  • 24
  • 59
  • This should work but how do I save the data as an XML. The data being returned is not correct. I think this is the crux of my problem. I am using the following code to get the data off of the web but it seems to be saving all of the HTML: Set objIE = CreateObject("InternetExplorer.Application") strIPaddress = xlsWS1.cells(j + 2, 3).Value objIE.Navigate ("http://api.geoiplookup.net/?query=" & strIPaddress) objIE.Visible = True str = objIE.Document.DocumentElement.innerHTML Call GetToken(str, "city") Thanks again for all of your help. – Sumeet Bains Dec 24 '17 at 18:01
  • Also I I was just able to save the the text on the page programmitically it would solve my issue very easily but that is where I stuck. – Sumeet Bains Dec 24 '17 at 18:23
  • @SumeetBains: you can get the xml part without HTML tags by using `Document.body.innerText` - if you have some trouble feel free to ask another question to get more detail. – deblocker Dec 25 '17 at 09:36