So, in column A I have an IP address. There will be many different
ones. I am trying to figure out the easiest and cleanest way to do
geolocation on the IP using this address (http://ipinfodb.com/
ip_query.php?ip=166.137.132.16) and have it update the IP address
based on the value in column A then put the output into columns B, C,
D (or whatever). Ideally I just want to get the Country, State and
City fields from the XML output.

Here is the XML output from the site:
166.137.132.16
OK
US
United States
34
New Jersey
New Milford
07646
40.933
-74.0177
-5.0
-4.0

Column 1
166.137.132.16
72.15.195.84
75.215.119.92
76.111.15.57
76.93.244.254
96.41.34.184
72.151.49.155


I have tried the Get External Data From Web but I have to do each one
manually. Any suggestions?

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
BaseURL = "http://ipinfodb.com/ip_query.php?ip="
TempURL = Sheet1.Range("A1").value

objHTTP.setTimeouts 0, 0, 0, 0
URL = BaseURL & TempURL
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible;
MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
SourceHTMLText = objHTTP.responseText

Put in a loop to grab your TempURL (IP addresses) from your sheet. Use the
returned SourceHTMLText to parse out the data elements you need. This works
for some pages, but YMMV as web pages that have embedded javascript elements,
etc may not always expose everything on the page as cleanly as one might like.

HTH,
Keth

"pand0ra.usa" wrote:

So, in column A I have an IP address. There will be many different
ones. I am trying to figure out the easiest and cleanest way to do
geolocation on the IP using this address (http://ipinfodb.com/
ip_query.php?ip=166.137.132.16) and have it update the IP address
based on the value in column A then put the output into columns B, C,
D (or whatever). Ideally I just want to get the Country, State and
City fields from the XML output.

Here is the XML output from the site:
166.137.132.16
OK
US
United States
34
New Jersey
New Milford
07646
40.933
-74.0177
-5.0
-4.0

Column 1
166.137.132.16
72.15.195.84
75.215.119.92
76.111.15.57
76.93.244.254
96.41.34.184
72.151.49.155


I have tried the Get External Data From Web but I have to do each one
manually. Any suggestions?

So I tried using the code and nothing happens. Any suggestions?


On Aug 20, 3:40*pm, ker_01 wrote:
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
BaseURL = "http://ipinfodb.com/ip_query.php?ip="
TempURL = Sheet1.Range("A1").value

* * * * objHTTP.setTimeouts 0, 0, 0, 0
* * * * * *URL = BaseURL & TempURL
* * * * * *objHTTP.Open "GET", URL, False
* * * * * *objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible;
MSIE 6.0; Windows NT 5.0)"
* * * * * *objHTTP.send ("")
* * * * SourceHTMLText = objHTTP.responseText

Put in a loop to grab your TempURL (IP addresses) from your sheet. Use the
returned SourceHTMLText to parse out the data elements you need. This works
for some pages, but YMMV as web pages that have embedded javascript elements,
etc may not always expose everything on the page as cleanly as one might like.

HTH,
Keth

"pand0ra.usa" wrote:
So, in column A I have an IP address. There will be many different
ones. I am trying to figure out the easiest and cleanest way to do
geolocation on the IP using this address (http://ipinfodb.com/
ip_query.php?ip=166.137.132.16) and have it update the IP address
based on the value in column A then put the output into columns B, C,
D (or whatever). Ideally I just want to get the Country, State and
City fields from the XML output.

Here is the XML output from the site:
166.137.132.16
* *OK
* *US
* *United States
* *34
* *New Jersey
* *New Milford
* *07646
* *40.933
* *-74.0177
* *-5.0
* *-4.0

Column 1
166.137.132.16
72.15.195.84
75.215.119.92
76.111.15.57
76.93.244.254
96.41.34.184
72.151.49.155

I have tried the Get External Data From Web but I have to do each one
manually. Any suggestions?

Ok, so I need some feedback on why my macro is not working.

I am pulling a URL from column C. Each row will have a different URL.
I want the macro to go through each row in column C and retrieve the
data from the URL and put it in the cell starting at column D. When I
run my macro nothing happens other then the cell D2 is selected.


Sub DownloadXML()
'
' DownloadXML Macro
'
' Keyboard Shortcut: Ctrl+h
'
' Select cell D2, *first line of data*.
Range("D2").Select

' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)

ActiveWorkbook.XmlMaps("Response_Map").Import Sheet1.Range
("$C2").Value

' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select

Loop

End Sub