Need to get location based on IP address in VBA

mcomp72

Active Member
Joined
Aug 14, 2016
Messages
275
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
Hey everyone. I would like the data in a few cells of my worksheet to be a different, based on where the user is located. I want to use their IP address to find where they are, and have figured out how to get their External IP address using VBA. However, what I haven't been able to figure out yet is how to then get the Country, State, City, and Zip Code from that IP address. I'm sure it must be possible to query an online IP database somehow to get that, but I have never dealt with querying databases before, so I haven't a clue how to do it. I'm wondering if anyone already has some code that does this that they'd be willing to share?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi Mcomp,
try something like this, it's using an online API to get the IP info
Cheers,
Koen
VBA Code:
Sub test()

Dim http As Object
Dim xmlDoc As MSXML2.DOMDocument60
Dim strURL As String

' requires reference to Microsoft XML 6.0

    IP = "8.8.8.8"
    strURL = "https://ipapi.co/" & IP & "/xml/"
        
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", strURL, False
    
    http.send
    Set xmlDoc = New MSXML2.DOMDocument60
    
    xmlDoc.LoadXML http.responseText
    
    Debug.Print http.responseText
    IPcity = xmlDoc.SelectSingleNode("//root/city").Text
    IPcountry = xmlDoc.SelectSingleNode("//root/country").Text

End Sub
 
Upvote 0
Thank you so much!! This works great!

Just an FYI to anyone else reading this who wants to try it, I had to add the following lines of code at the beginning of the test sub:

VBA Code:
Dim IP as String
Dim IPcity As String
Dim IPcountry As String
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top