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:
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:
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!