Excel 2010 VBA - Pull Table from website

[rcoll20]

Trying to pull weather forecast using https://www.wunderground.com/cgi-bin/findweather/getForecast?. I would like to pull the future high forecast for upcoming dates. I am having troubling pulling the table, does anyone have any advice for me? Code is posted below.

Sub Weather_Scrape()
Dim counter As Integer
Dim Doc As HTMLDocument
counter = 0
Dim zipcodes As Range
Set zipcodes = Range("D5:D30")
'For each: in zipcodes then do a webscrape
Dim IE As New InternetExplorer
IE.Visible = True
Dim sSpan As String
Dim tSpan As String
Dim dd As HTMLDocument

' For Each i In zipcodes
' If i <> "" Then
' IE.navigate "https://www.wunderground.com/cgi-bin/findweather/getForecast?query=" & i.Value
' Do
' DoEvents
' Loop Until IE.readyState = READYSTATE_COMPLETE
' Set Doc = IE.document
' sSpan = Doc.getElementById("curTemp").innerText
' Range("E5").Offset(counter, 0) = sSpan
' counter = counter + 1
' End If

' Next i
' counter = 0
For Each i In zipcodes
If i <> "" Then
IE.navigate "https://www.wunderground.com/cgi-bin/findweather/getForecast?query=" & i.Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Set Doc = IE.document
tSpan = Doc.getElementsByTagName("Div").inntertext
Range("F5").Offset(counter, 0) = tSpan
counter = counter + 1
End If
Next i


IE.Quit


End Sub

 

[Rijnsent]

Hi Rcoll,

it took some trial and error, but this works for me. Note: it takes some searching in the source of the website to find that the table with forecasts is in a DIV called "flot-header". If you look that one up (in chrome: right click on the page, select inspect/source and search for "Sun 07/24", then move up some levels to find "flot-header". In that div, there are a bunch of days, so this macro loops through them and checks if they have contents, as the not-visible ones don't have contents. If they do, the macro will put the maximum temperature ("cond-high") in the cells of that row.

Cheers,

Koen

Sub Weather_Scrape()

Dim counter As Integer
Dim dd As HTMLDocument
Dim xobj As HTMLDivElement
Dim tobj As HTMLDivElement
Dim sSpan As String

Dim zipcodes As Range
Set Sht = ActiveSheet
Set zipcodes = Sht.Range("D5:D30")

Dim IE As New InternetExplorer
IE.Visible = True

counter = 0

'Loop through all the zipcodes
For Each i In zipcodes
    If i <> "" Then
        IE.navigate "https://www.wunderground.com/cgi-bin/findweather/getForecast?query=" & i.Value
        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        'Scroll down and wait 5 seconds for the table to load
        IE.document.parentWindow.scroll 0&, 300&
        Application.Wait (Now + TimeValue("0:00:05"))
        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE
        
        Set dd = IE.document
        Set xobj = dd.getElementsByClassName("flot-header").Item(0)
        col = 0
        
        'Loop through all the days in the flot-header item
        For Each tobj In xobj.ChildNodes
            col = col + 1
            If tobj.Children.Length = 2 Then
                If tobj.Children(0).className = "col-title" Then
                    'Debug.Print tobj.Children(0).innerHTML
                    Sht.Cells(zipcodes.Row - 1, i.Column + col).Value = tobj.Children(0).innerHTML
                End If
                If tobj.Children(1).className = "col-body" Then
                    If tobj.Children(1).innerHTML <> "" Then
                        'Debug.Print tobj.getElementsByClassName("cond-high").Item(0).innerHTML
                        Sht.Cells(i.Row, i.Column + col).Value = tobj.getElementsByClassName("cond-high").Item(0).innerHTML
                    End If
                End If
            End If
        Next tobj
    End If
Next i

IE.Quit

End Sub

 

[rcoll20]

Thanks for the help. Do you know how I can pull in 2015 historical weather data from the website to compare this year to last year?

https://www.wunderground.com/histor...reqdb.zip=48823&reqdb.magic=1&reqdb.wmo=99999

 

[Rijnsent]

Hi Rcoll,

this url probably pulls in the info you're looking for?
https://www.wunderground.com/histor...=48823&reqdb.magic=1&reqdb.wmo=99999&format=1
From that URL, quite a bit can be removed: https://www.wunderground.com/histor.../MonthlyHistory.html?reqdb.zip=20505&format=1
So for every zip code you want to get, get the airport code nearby and the zip code to build the URL and use code like the one I gave you before to pull it in.

If you have some code and get stuck, please do post it (in [ CODE ] brackets).

Cheers,

Koen