Stuck trying to use VBA to parse html

Question

I'm trying to teach myself the basics of VBA and XML to solve a specific problem I'm working on in Excel. I'm trying to use an API to pull census block IDs given lat/lon.

Here's the XML code:

<Response xmlns="http://data.fcc.gov/api" status="OK" executionTime="120">
  <Block FIPS="120950170151016">
    <intersection FIPS="120950170151016"></intersection>
    <intersection FIPS="120950170151019"></intersection>
    <intersection FIPS="120950170151015"></intersection>
  </Block>
  <County FIPS="12095" name="Orange"></County>
  <State FIPS="12" code="FL" name="Florida"></State>
  <messages>FCC0001: The coordinate lies on the boundary of mulitple blocks, first FIPS is displayed. For a complete list use showall=true to display 'intersection' element in the Block</messages>
  <head></head>
</Response>

The only VBA command I can get to work is

blockID = Doc.getElementsByTagName("Block")(0).innerText

which gives me the value with lots of html code still attached, as in

<Block FIPS="120950170151016">

Searching around, it seems like I might need to use the getAttributes function(?) but that doesn't appear to be an option in the dropdown as I'm using VBA. Which makes me wonder if I don't have one of the reference packages installed.

Any insights?

Edit: Thanks for insights below. I've tried using Load() instead of LoadXML() but it still doesn't seem to read any information into the object. The error is "The data necessary to complete this operation is not yet available" and it happens when the code tries to loop through the x values. Clear from the Locals viewer that there's no data in the object. Here's what I've got:

Dim oAttribute, item
Dim x As Long
Dim apiURLstub, apiURL As String
apiURLstub = "http://data.fcc.gov/api/block/find?"

'append lat/lon info to URL
Dim lat As Double
Dim lon As Double

lat = Range("A3").Value
lon = Range("B3").Value
apiURL = apiURLstub & "latitude=" & lat & "&longitude=" & lon & "&showall=true"

Dim objXML As Object, node As Object
Set objXML = New MSXML2.DOMDocument

If Not objXML.Load(apiURL) Then  'strXML is the string with XML'
    Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason

Else
    Set node = objXML.getElementsByTagName("intersection")
    For x = 0 To node.Length - 1
        For Each oAttribute In node(x).Attributes
            Debug.Print oAttribute.Value
        Next
    Next

End If

XML != HTML. Use [Microsoft.XMLDOM](https://msdn.microsoft.com/en-us/library/aa468547.aspx) instead. — Comintern, Nov 18 '16 at 14:26
Have you tried using [Regex](http://stackoverflow.com/a/1732454/1641172)? — , Nov 18 '16 at 14:54

score 0 · Answer 1 · edited May 23 '17 at 12:07

In my answer to VBA XML parsing - looping through child nodes I have 2 animated Gifs that show how to use the Locals and Immediate windows to tranverse throught the properties and build you code as you go.

Sub TestStub()
    Dim oAttribute, item
    Dim x As Long
    Const XMLTEST = "<Response xmlns=""http://data.fcc.gov/api"" status=""OK"" executionTime=""120"">" & _
                    "<Block FIPS=""120950170151016"">" & _
                    "<intersection FIPS=""120950170151016""></intersection>" & _
                    "<intersection FIPS=""120950170151019""></intersection>" & _
                    "<intersection FIPS=""120950170151015""></intersection>" & _
                    "</Block>" & _
                    "<County FIPS=""12095"" name=""Orange""></County>" & _
                    "<State FIPS=""12"" code=""FL"" name=""Florida""></State>" & _
                    "<messages>FCC0001: The coordinate lies on the boundary of mulitple blocks, first FIPS is displayed. For a complete list use showall=true to display 'intersection' element in the Block</messages>" & _
                    "<head></head>" & _
                    "</Response>"


    Dim objXML As Object, node As Object

    Set objXML = CreateObject("MSXML2.DOMDocument")

    If Not objXML.LoadXML(XMLTEST) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason

    Else
        Set node = objXML.getElementsByTagName("intersection")
        For x = 0 To node.Length - 1
            For Each oAttribute In node(x).Attributes
                Debug.Print oAttribute.Value
            Next
        Next

    End If
End Sub

Thanks for sharing this code. It's very helpful, especially to this beginner. — jer312, Nov 21 '16 at 17:45
At the risk of asking another dumb question, I'm still having trouble getting my VBA program to read in the xml data from the URL. I've tried using Load() instead of LoadXML() but it doesn't appear to be reading any information into the object. Here's what I have: — jer312, Nov 21 '16 at 17:46
See updated post above. Thanks for taking the time to answer some basic questions. — jer312, Nov 21 '16 at 17:53

Stuck trying to use VBA to parse html

1 Answers1