Extracting WEB PAGE data from a hidden menu that is revealed after clicking

user_13039887 0
Viewed 159

New to VBA and going in circles trying to extract data from a table on a webpage that only appears after it is clicked.

I'm using a VBA macro within excel to pull data from a weather.com:

  1. I can successfully extract elements from the visible table.
  2. I'm pulling Error 91 when trying to pull the "sunrise" and "sunset" time from the hidden menu.
  3. I would like to extract the time of sunrise and sunset from the 2nd day on the table.

Question:

  1. Is there a way to click within the code to reveal the data I want to extract?
  2. This data is located within a SPAN in the source code - Can I be extract this innerText from the closest ClassName to the SPAN in question?

Would somebody mind taking a look at the code and helping me out? Thanks!

I can successfully extract "day", "weather", and "temp" but cannot pull "srise".

Here is my VBA code:

Sub Get_Lancaster()
Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim weather As Variant
Dim temp As Variant
Dim day As Variant
Dim srise As Variant

website = "https://weather.com/weather/5day/l/2db548c2f0fb03c25c0d5c5520a32877082d295d907b06df5eff91cd140165b9"

Set request = CreateObject("MSXML2.XMLHTTP")

request.Open "GET", website, False

request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

request.send

response = StrConv(request.responseBody, vbUnicode)

html.body.innerHTML = response

weather = html.getElementsByClassName("description").Item(2).innerText
temp = html.getElementsByClassName("temp").Item(2).innerText
day = html.getElementsByClassName("day-detail clearfix").Item(1).innerText
srise = html.getElementsByClassName("sunrise").Item(1).innerText

Range("B3").Value = day
Range("D3").Value = weather
Range("E3").Value = temp
Range("G3").Value = srise

End Sub

Here is the source HTML code for the menu after it is clicked and revealed on the source page:

I am trying to extract the 7:05am located in the bottom SPAN:

<tr classname="clickable open" class="clickable open">
<td class="twc-sticky-col cell-hide">
    <div classname="twc-table-shadow sticky" class="twc-table-shadow sticky"></div>
  </td>

<td headers="uv-index" title="Partly cloudy skies. High around 40F. Winds light and variable." data-track-string="ls_24_hour_ls_24_hour_toggle" classname="uv" class="uv">
    <span class="">3 of 10</span>
  </td>

<td headers="sunrise" title="Partly cloudy skies. High around 40F. Winds light and variable." data-track-string="ls_24_hour_ls_24_hour_toggle" classname="sunrise" class="sunrise">
  <div>
    <span classname="icon icon-font iconset-astro dark icon-sunrise" class="icon icon-font iconset-astro dark icon-sunrise">
    </span>
    <span>7:05 am</span>
  </div>
</td>

Thanks for taking a look and helping a complete newbie who's in over their head!

-J

extract vba excel html
1 Answers

You've done a good job so far, +1 for the effort you've put in this!

I would like to take it one step further, if you don't mind.

Firstly, you have to keep in mind that what you see when you inspect an element in your browser's developer tools is not necessarily what you'll see in the source HTML of the page.

Indeed, in this case if you actually right click and view the source code of the page, you will not find the HTML snippet that you posted. You will find it however if you inspect the element of interest.

This happens because this part of the HTML code is generated by a script. You will find this script in the Page's source code inside a <script></script> tag (just search for "sunrise" for example). This tag contains a huge string and a second small one, both in JSON format.

The script tag looks like this:

<script charSet="UTF-8">window.__data={"transactionId":"a3520089-63d0-4320-bb6e-c6308b6e820d", ... {"startIndex":0}};window.experience={"connectionSpeed":"4g","deviceClass":"desktop"};</script>

I have replaced most of the string with ... for the sake of readability.

All the data you need is in the first JSON string (window.__data) between the curly brackets {...}. You will not be needing the second string (window.experience).

So basically what you need is to isolate this string from the response and then parse it to get the info you want.

You can inspect the JSON string's structure using a tool like this. Here's how it looks like:

enter image description here

To parse a string like this you will need to add this JSON parser to your project. Follow the installation instructions in the link and you should be set to go.

You will also need to add the following references to your project (VBE>Tools>References):

Microsoft XML version 6.0
Microsoft Scripting Runtime

Having said that, here's how I would do it:

Option Explicit

Sub weather()
Dim req As New MSXML2.XMLHTTP60
Dim url As String, data As String, startOfData As String, endOfData As String
Dim dataJSON As Object, day As Object

startOfData = "window.__data=" 'the string of interest starts after this
endOfData = ";window.experience=" 'the string of interest ends before this

url = "https://weather.com/weather/5day/l/2db548c2f0fb03c25c0d5c5520a32877082d295d907b06df5eff91cd140165b9"
With req
    .Open "GET", url, False
    .send
    data = .responseText
End With

data = Mid(data, InStr(1, data, startOfData) + Len(startOfData)) 'isolate the string of interest: step 1
data = Mid(data, 1, InStr(1, data, endOfData) - 1) 'isolate the string of interest: step 2

Set dataJSON = JsonConverter.ParseJson(data)
Set dataJSON = dataJSON("dal")("DailyForecast")("geocode:44.49,-71.57:language:en-US:units:e")("data")("vt1dailyForecast")

For Each day In dataJSON
    Debug.Print day("sunrise")
    Debug.Print day("day")("narrative")
    Debug.Print day("night")("narrative")
Next day

End Sub

For demonstration purposes, the code above only prints the sunrise and the narrative of each day in the immediate window. Having in mind how the JSON is structured and following the same logic, you can adjust the code to print whichever parameter you need.

Here's a sample of the output:

enter image description here

user_99358270
Related