VBA HTML elements to Excel

Question

I am working on a code that uses VBA-Excel to navigate to a website and copy some values to Excel.

I can open the website and navigate, but I can't save the "Precipitation" values in excel sheet

Sub accuweather()

Dim ie As InternetExplorer
Dim pagePiece As Object
Dim webpage As HTMLDocument

Set ie = New InternetExplorer
ie.Visible = True 

ie.navigate ("http://www.accuweather.com/en/pt/abadia/869773/daily-weather-forecast/869773?day=2")

Do While ie.readyState = 4: DoEvents: Loop
Do Until ie.readyState = 4: DoEvents: Loop

While ie.Busy
    DoEvents
Wend

Set webpage = ie.document
Set mtbl = webpage.getElementsByTagName("details-card card panel details allow-wrap")
Set table_data = mtbl.getElementsByTagName("div")(1)

For itemNum = 1 To 240
    For childNum = 0 To 5
        Cells(itemNum, childNum + 1) = table_data.Item(itemNum).Children(childNum).innerText
    Next childNum
Next itemNum

ie.Quit
Set ie = Nothing

End Sub

This is not a tag name details-card card panel details allow-wrap Looks like multi-value class — QHarr
– QHarr, Commented Nov 22, 2019 at 19:03

QHarr · Accepted Answer · 2019-11-22 21:33:06Z

2

The method you are using is getElementsByTagName but the reference is for a multi-valued class. So the correct method would be getElementsByClassName.

However, you don't need the browser as that content is static and you can just use faster xmlhttp request and a single (more robust and faster) class to target.

This

html.querySelectorAll(".list")

is retrieving the two parent nodes which have the various p tag children. The first child in both cases

.Item(i).FirstChild

is the precipitation info.

Option Explicit

Public Sub GetPrecipitationValues()
    Dim html As MSHTML.HTMLDocument, i As Long

    Set html = New MSHTML.HTMLDocument

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.accuweather.com/en/pt/abadia/869773/daily-weather-forecast/869773?day=2", False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        html.body.innerHTML = .responseText
    End With

    With html.querySelectorAll(".list")
        For i = 0 To .Length - 1
            Debug.Print .Item(i).FirstChild.innerText
        Next
    End With
End Sub

edited Nov 22, 2019 at 21:33

answered Nov 22, 2019 at 19:18

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

QHarr Over a year ago

I am printing results to immediate window Ctrl + G but easy to write to sheet

PedroPinto Over a year ago

Thanks QHarr. how can i print this results to excel?

PedroPinto Over a year ago

Qharr sorry but i need to add a loop for each line URL (multiple URLS)

QHarr Over a year ago

There are lots of example on stackoverflow showing you how to do this. I will share a link to some below so you can have a go at it first. Have a look through these: stackoverflow.com/search?q=Dim+urls+user%3A6241235+is%3Aanswer

PedroPinto Over a year ago

i posted these question on stackoverflow.com/questions/59011071/excel-vba-loop . if you can help i will appreciate. tks again!

Collectives™ on Stack Overflow

VBA HTML elements to Excel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related