html table scraping using vba

Question

im trying to get the country from the tr tag but it just gives me the first row of the table how do i scrape a specific row

Sub ipsearch()
    Dim x As Integer
    x = 2

    Do Until x = 4000   

        Dim ie As New InternetExplorer    
        ie.navigate "https://whatismyipaddress.com/ip/" & Range("E" & x).Value

        Do    
            DoEvents        
        Loop Until ie.readyState = READYSTATE_COMPLETE

        Dim doc As HTMLDocument    
        Set doc = ie.document    
        Dim sDD As String

        sDD = Trim(doc.getElementsByTagName("td")(, 0).innerText)

        Range("F" & x).Value = sDD    
        x = x + 1    
    Loop

End Sub

the blue bit is what I get and the yellow is what i want

Vityata · Answer 1 · 2017-12-18T15:59:31.477

This is something, that will return "United Kingdom" in your example:

Sub ipsearch()
    Dim x As Long
    x = 2

    Do Until x = 4000
        Dim ie As New InternetExplorer
        ie.navigate "https://whatismyipaddress.com/ip/" & "2.99.247.66"

        Do
            DoEvents

        Loop Until ie.readyState = READYSTATE_COMPLETE
        Dim doc As HTMLDocument
        Set doc = ie.document
        Dim sDD As String
        ie.Visible = True

        sDD = Trim(doc.getElementsByTagName("tbody")(1).innerText)
        Range("F" & x).Value = Split(sDD, vbCrLf)(5)

        x = x + 1

    Loop

End Sub

In general, a few ideas for writing better code.

It is really good to know what you are using - e.g., in your screenshot, the numbers from the IP are taken from the line above the blue, e.g. from "Details for 2.99.247.66".
When you give code to StackOverflow, make sure to mention the additional libraries that your code is using. In your case these two:

Or make sure that your code uses late binding, thus the libraries should not be added. - In general, consider using Long, in stead of Integer - Why Use Integer Instead of Long?

Format your code, when you submit it. It looks a bit better. Ctrl+K is the shortcut in StackOverflow.
Use Option Explicit for VBA.
Try to hardcode the variables in your question. In your case:

"https://whatismyipaddress.com/ip/" & "2.99.247.66"

Last but not least, a lot of web sites do not like being cralwed. Just fyi - https://www.google.com/search?q=is+crawling+legal

SIM · Answer 2 · 2017-12-18T15:55:21.360

Give this a shot. It will fetch you the country name and place it in Range("A1").

Sub ipsearch()
    Dim IE As New InternetExplorer, html As HTMLDocument
    Dim post As Object

    With IE
        .Visible = False
        .navigate "https://whatismyipaddress.com/ip/2.99.247.66"
        Do Until .readyState = READYSTATE_COMPLETE: Loop
        Set html = .document
    End With

    For Each post In html.getElementsByTagName("th")
        If InStr(post.innerText, "Country:") > 0 Then [A1] = post.ParentNode.LastChild.innerText: Exit For
    Next post
    IE.Quit
End Sub

Reference to add to the library:

1. Microsoft Internet Controls
2. Microsoft HTML Object Library

And to make it way faster, try the below one:

Sub ipsearch()
    Dim HTTP As New XMLHTTP60, html As New HTMLDocument
    Dim post As Object

    With HTTP
        .Open "GET", "https://whatismyipaddress.com/ip/2.99.247.66", False
        .send
        html.body.innerHTML = .responseText
    End With

    For Each post In html.getElementsByTagName("th")
        If InStr(post.innerText, "Country:") > 0 Then [A1] = post.ParentNode.LastChild.innerText: Exit For
    Next post
End Sub

Reference to add to the library:

1. Microsoft XML,v6.0  ''or the version you have
2. Microsoft HTML Object Library

Output:

United Kingdom

html table scraping using vba

2 Answers2