CODEWITHSUNDEEP
vbaexcelgetelementsbytagname
Nahuatl
Nahuatl

Reputation: 132

Excel VBA getElementsByTagName - Runtime error - 424

I'm using the following references: Visual Basic for Applications, Microsoft Excel 16.0 Object Library, OLE Automation, Microsoft Office 16.0 Object Library, Microsoft Internet Controls.

I've singled out the line below where I'm running into this error. I've tried using innerText and textContent without any success.

I've also tried the following this methodoly: http://automatetheweb.net/vba-getelementsbytagname-method/ without any success as I run into a runtime error 70.

I've tried to getElementsByClassName.getElementsByTagName without any success.

I've tried posting this question in Microsoft Community answers but I've had very little success using that site in general in the past.

Sub ZipCodeRetrieve()

Dim ZipCodeRange As Range
Dim PopDensity As IHTMLElementCollection
Dim PopChange As IHTMLElementCollection
Dim IE As Object
Dim HTMLdoc As HTMLDocument

'Creates ie instance
With ThisWorkbook.Sheets("ZipCodes")
Set IE = New InternetExplorer
IE.Navigate "http://mcdc.missouri.edu/websas/caps10c.html"
IE.Visible = True

Set ZipCodeRange = Range("A2", .Range("A2").End(xlDown))
Debug.Print ZipCodeRange.Address

For Each cell In ZipCodeRange

    'allows ie to load before proceeding
    While IE.busy
        DoEvents
    Wend

    'looks for search box in Missouri.edu and inputs zipcode
    IE.document.all("latitude").Value = cell.Value
    'radius is constant
    IE.document.all("radii").Value = 75
    'clicks enter
    IE.document.forms(0).submit

    'allows ie to load before proceeding
    Do While IE.busy
        DoEvents
    Loop

    'preps ie for data collection
    Set HTMLdoc = IE.document

THIS IS WERE I'M GETTING THE ERROR

 Set PopDensity = HTMLdoc.getElementsByTagName("b").Item(18).innerText


    Set PopChange = HTMLdoc.getElementsByTagName("b").Item(10).textContent

    Debug.Print PopDensity.Value
    Debug.Print PopChange.Value

Next cell

End With

End Sub

Upvotes: 0

Views: 743

Answers (1)

Tim Williams
Tim Williams

Reputation: 166735

Set PopDensity = HTMLdoc.getElementsByTagName("b").Item(18).innerText

You've declared PopDensity as IHTMLElementCollection but here you're assigning a value from innerText, which as the method name suggests return text, not a collection.

Try this:

Dim PopDensity 'As String 'or leave as variant
PopDensity = HTMLdoc.getElementsByTagName("b").Item(18).innerText

Upvotes: 1

Related Questions