Need to improve weather forecast VBA

[Js Smith]

HI all! I had a VBA based on MSN but it's recently started to pull the current location weather no matter what I do so I'm starting over using weather.com.
The switch is due to website behavior at msn; even when typing a desired url, it goes to current location and trying to learn more about vba.

The idea is the end user will enter whatever city they need and the VBA will concatenate and navigate, so in cells g1 and h1 is:

Seattle

WA

Code is:

Sub Weather3()

   Dim HTTP As Object
   Dim HTML As Object
   Dim i As Integer
   Dim j As Integer
   Dim city As String
   Dim state As String
       
          Range("A:B").ClearContents
          
    Set HTML = CreateObject("HTMLFILE")
    Set HTTP = CreateObject("MSXML2.XMLHTTP")
    
'       'Delete_IE_Cache()            // commented out now to speed testing up
'    Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 11", vbHide
    
    Range("G1").Select
    city = ActiveCell.Value
    state = ActiveCell.Offset(0, 1).Value
    
'    myURL = "https://weather.com/weather/tenday/l/" & state & "+" & city & ""    // what I'd rather use
    myURL = "https://weather.com/weather/tenday/l/Seattle+WA"   // used to test the rest of code works
    
    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    // gets the date
            Range("B" & j) = objCollection(i).PreviousSibling.FirstChild.innerText                              // gets the temps

        End If
        i = i + 1
    Loop

End Sub

Questions:
Why

myURL = "https://weather.com/weather/tenday/l/" & state & "+" & city & ""

doesn't work but

myURL = "https://weather.com/weather/tenday/l/Seattle+WA"

does?
I use this concatenation method in my MSN report and it works. In this report there is no error, columns A & B are just not populated.

How do I get the percentage for precipitation in column C? it doesn't look like a child to me but my HTML knowledge is not vast.

Thanks, in advance, for any help you can provide!! =D

 

[johnnyL]

Questions:
Why

myURL = "https://weather.com/weather/tenday/l/" & state & "+" & city & ""

doesn't work but

myURL = "https://weather.com/weather/tenday/l/Seattle+WA"

does?
I use this concatenation method in my MSN report and it works. In this report there is no error, columns A & B are just not populated.

I think you need to swap your state and city to city and state.
You probably need another quote or two on the end.

 

[Js Smith]

I think you need to swap your state and city to city and state.
You probably need another quote or two on the end.

Ha! Must be Monday.
Yup,

    myURL = "https://weather.com/weather/tenday/l/" & city & "+" & state & ""

works MUCH better! =P

Thanks johnnyL!

Any ideas on how to get the precipitation in the report?

 

[johnnyL]

I'm not familiar with MSN report so I can't test that part. Sorry.