I am trying to see if anyone can help me in making this Excel VBA code a little faster in rendering the data from the weather site I am using. Maybe instead of getting all 14 days it includes in the site, could I get help in getting just 10 days? Any help is much appreciated. Thanks!

Sub MiamiWeather()

   Dim HTTP As Object, HTML As Object, i As Integer, j As Integer
    Set HTML = CreateObject("HTMLFILE")
    Set HTTP = CreateObject("MSXML2.XMLHTTP")
    myURL = "https://weather.com/weather/tenday/l/3881cd527264bc7c99b6b541473c0085e75aa026b6bd99658c56ad9bb55bd96e"
    HTTP.Open "GET", myURL, False
    HTML.body.innerHTML = HTTP.responseText
    Set objCollection = HTML.getElementsByTagName("p")
    i = 0
    Do While i < objCollection.Length And j < 20
        If objCollection(i).getAttribute("data-testid") = "wxPhrase" Then
            j = j + 1
            Range("A" & j) = objCollection(i).PreviousSibling.PreviousSibling.FirstChild.innerText
            Range("B" & j) = objCollection(i).PreviousSibling.FirstChild.innerText
        End If
        i = i + 1
    Set HTML = Nothing
    Set HTTP = Nothing
End Sub
  • 1
    \$\begingroup\$ Can you please change your quesiton title into a brief description what that code is supposed to do? As is it's too generic now. \$\endgroup\$ – πάντα ῥεῖ Sep 29 at 23:34
  • \$\begingroup\$ If you want to load fewer days just add a condition on the loop: Do While i < objCollection.Length And j < 10 \$\endgroup\$ – porkaloca Sep 30 at 0:26
  • \$\begingroup\$ Thanks, Porkaloca. \$\endgroup\$ – Marcos Gomez Sep 30 at 18:35
  • 1
    \$\begingroup\$ Using some simple timing code (e.g. Debug.Print "Section:"; (Timer - stTime)*1000;"ms") I ran and profiled your code. About 50% of the running time is spent in that While loop, 30% is spent loading the HTML document from the HTTP response, the other 20% spent in the Get request. Consequently I'd say the best optimisations come from the while loop, and you may want to profile that in more detail. It's true, stopping after j = 10 rather than 14 saves ~ 25% of the loop time, however looping over all <p> elements is wasteful and you may want to try getElementsByClassName to narrow the search \$\endgroup\$ – Greedo Oct 1 at 16:27
  • \$\begingroup\$ Thanks for timing the code, Greedo. I was working with a code that had getElementsByClassName, but it stopped giving me the first date in the forecast of today's forecast of the summary. \$\endgroup\$ – Marcos Gomez Oct 3 at 23:56

The real question here is how to get only the p tags with a data-testid with the value of wxPhrase. This can be done using querySelectorAll().


See Document.querySelectorAll().

You will need to add a reference to the Microsoft HTML Object Library and declare you document variable as HTMLDocument.

Dim Document As HTMLDocument

Reference Window

Loading the values into an Array will offer you the best performance. It will not make a noticeable difference if you return 10 days or all the days of the month.

Refactored Code

Sub MiamiWeather()
    Dim Data As Variant
    Data = MiamiWeatherData
    Range("A1:B1").Value = Array("Date", "Tempature")
    Range("A2").Resize(UBound(Data), 2).Value = Data
End Sub

Function MiamiWeatherData()
    Const URL = "https://weather.com/weather/tenday/l/3881cd527264bc7c99b6b541473c0085e75aa026b6bd99658c56ad9bb55bd96e"
    Dim responseText As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL, False
        responseText = .responseText
    End With
    Dim Document As HTMLDocument
    Set Document = CreateObject("HTMLFILE")
    Document.body.innerHTML = responseText

    Dim Children As IHTMLDOMChildrenCollection
    Set Children = Document.querySelectorAll("p[data-testid='wxPhrase']")
    Dim Results As Variant
    ReDim Results(1 To Children.Length, 1 To 2)
    Dim r As Long
    For r = 0 To Children.Length - 1
        Results(r + 1, 1) = Children(r).PreviousSibling.PreviousSibling.FirstChild.innerText
        Results(r + 1, 2) = Children(r).PreviousSibling.FirstChild.innerText
    MiamiWeatherData = Results
End Function
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.