(Solved) – Pulling data from a website address based on Excel values

(solved)-–-pulling-data-from-a-website-address-based-on-excel-values

I have a problem writing a macro for Excel VBA and couldn’t find the exact answer to what I’m looking for.

I have the following URL:

http://ratings.ambest.com/companyprofile.aspx?ambnum=2257

And I have a list of IDs in Column A of my Excel worksheet that I need to feed into the last part of that URL (changing the 2257 to, say, 73132 and 1996 in the example). Then, I need to fetch the credit rating on the resulting webpage, which would be following field:

div id=”MainContent_FSRandICR_AffilCodeDiv”

So, I need a macro that:

  • Changes the last section of the URL based on the values on Column A
    of the worksheet.
  • Pulls data from the field with the tag div id=”MainContent_FSRandICR_AffilCodeDiv”
  • Parses it into the same Excel worksheet into Column B, so for each ID I’d have its credit rating.
  • Repeats until EOF

I managed to make a macro based on this video, but there are many issues with it, namely:

  • I have to input each ID manually, whereas I want it to read from values in a given column automatically
  • It fetches the value from the tag “td” that gives me more data than I want (I couldn’t get it to work with div id).

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Row = Range("Code").Row And _
        Target.Column = Range("Code").Column Then
        Dim IE As New InternetExplorer
        IE.Visible = True
        IE.navigate "http://ratings.ambest.com/companyprofile.aspx?ambnum=" & Range("Code").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")(1).innerText)
        IE.Quit
        Dim aDD As Variant
        aDD = Split(sDD, ",")
        Range("AMBest").Value = aDD(0)
        End If
    End Sub

Many thanks in advance for your help!

Leave a Reply

Your email address will not be published. Required fields are marked *