# Excel VBA code that displays a 14 day weather forecast

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")

HTTP.Open "GET", myURL, False
HTTP.send

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
Loop

Set HTML = Nothing
Set HTTP = Nothing
End Sub

• If you want to load fewer days just add a condition on the loop: Do While i < objCollection.Length And j < 10
– DT1
Sep 30 '20 at 0:26
• 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 Oct 1 '20 at 16:27
• 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. Oct 3 '20 at 23:56
• Could I easily change the code to getElementsByClassName? Oct 4 '20 at 0:21

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().

Document.querySelectorAll("p[data-testid='wxPhrase']")

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

Dim Document As HTMLDocument

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()
Dim responseText As String
With CreateObject("MSXML2.XMLHTTP")
.Open "GET", URL, False
.send
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
Next

MiamiWeatherData = Results
End Function

• I think you forgot to swop out your late-bound htmlfile for early bound New MSHTML.HTMLDocument Jan 28 at 20:46
• @QHarr I didn't bother adding a reference to the to Microsoft XML, v?.0. I'm referencing Microsoft HTML Object Library so that I will have intellisense for the HTML elements. Jan 28 at 21:48
• I mean I think this Set Document = CreateObject("HTMLFILE") should be Set Document = New MSHTML.HTMLDocument to stick with early binding. ` Jan 28 at 22:37