Not a Secret Anymore – What is my IP Address Location? Bulk GeoIP Lookup

GeoIP to Location Lookup Database – Find Website IP

Many websites and software provide free GeoIP – IP to location lookup  services.

If we input an IP address to these services, they provide us Excel IP location address lookup facility with below listed information to help us understand the actual address of the User of that IP.

• Country
• Region or State
• City
• Address
• ZIP
• Latitude & Longitude
• ISP (Service Provider)

These location information are allocated & maintained by Regional Internet Registry (Refer this Wiki Page for Detailed info) for different geographic locations.

The Geoip Lookup service providers collect this information or query the corresponding Registry and provide us the Location Data for an IP Address.

Lets see how we can query one such service and get ip address location in an Excel Sheet.

1. Excel IP Address – Location Lookup using Geoip Service

We are going to use the API provided by web service http://freegeoip.net/ to create an Excel ip Address lookup application.

From this API we can get locations details of ip addresses either in JSON, CSV or XML formats. This API limits number of requests per hour to 10,000 queries.

The below sample code can fetch the JSON format to your Excel Sheet. Once the data is received, then it can be parsed to separate fields.

Try the Free Excel Download to Locate a list of IP address to its Geo location - Bulk Geoip Lookup

Downloaded 4,240 Times

Note: Before trying this code, add a reference to “Microsoft HTML object library” from Tools -> Reference in VB Editor. This enables us to fetch details returned from the website.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'          Visit our website for more Tips and Tricks                      '
'                ---- Officetricks.com ----                                '
'                                                                          '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'
''''!!!!!!!!!!!!!!!IMPORTANT NOTE!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
''''Before Executing this code add the below reference
''''Add reference to Microsoft HTML Object Library from Tools->Reference
'
'
Public Sub get_Geo_Location_From_IP()
    Dim xhttp_get As Object
    Dim oRow As Integer

    READYSTATE_COMPLETE = 4
    oRow = 2
    IpVal = VBA.Trim(Sheets("Input").Cells(oRow, 1))

    While IpVal <> ""
        Set xhttp_get = CreateObject("MSXML2.XMLHTTP")
        With xhttp_get
            .Open "Get", "http://freegeoip.net/json/" & IpVal, True
            .send
        Do Until xhttp_get.readyState = READYSTATE_COMPLETE
            DoEvents
        Loop
            Set xml_doc = New MSHTML.HTMLDocument
            xml_doc.body.innerHTML = .responseText
        End With
        
        Sheets(1).Cells(oRow, 2) = xml_doc.body.innerHTML
        
        Set xhttp_get = Nothing
        oRow = oRow + 1
        IpVal = VBA.Trim(Sheets("Input").Cells(oRow, 1))
    Wend

    MsgBox "Task Completed - Visit http://OfficeTricks.com for more Useful Apps"

End Sub

2. GeoIp Location Lookup From MAXMIND – GEOLITE Database

MAXMIND provides free downloadable Geoip lookup database in CSV/Binary formats, to use it in your applications.

Download files can be found in this path – http://dev.maxmind.com/geoip/legacy/geolite/#Downloads

These files have mapping between IP Range and corresponding Country or Region name. They have a version of Creative Commons License and also provide a Commercial redistribution version. Read their documentations in detail before using their database in your applications.

Only 2 of the possible solutions to trace IP address to its Geo Location, is provided here. There are plenty of other possible methods available for everyone to try. If you find anything interesting, leave a comment for the benefit of the readers.

Try the Free Excel IP address to location Lookup - Find Website IP Address to Geographic location

Downloaded 4,240 Times

Remember that this Geoip to location lookup database from Maxmind keep changing frequently. They get updates from ip address allocations from ISPs & update their database accordingly for every geo location around the globe. So, read their Automation process to keep your applications upto date with Geoip lookup.