Need to improve weather forecast VBA

Js Smith

New Member
Joined
Jul 24, 2020
Messages
44
Office Version
  1. 2010
Platform
  1. Windows
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:
SeattleWA

Code is:
VBA Code:
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
VBA Code:
myURL = "https://weather.com/weather/tenday/l/" & state & "+" & city & ""
doesn't work but
VBA Code:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Questions:
Why
VBA Code:
myURL = "https://weather.com/weather/tenday/l/" & state & "+" & city & ""
doesn't work but
VBA Code:
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.
 
Last edited:
Upvote 0
Solution
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,
VBA Code:
    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?
 
Upvote 0
I'm not familiar with MSN report so I can't test that part. Sorry.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top