1

I was able to use VBA to extract a table from HTML source. My problem now is that I need the information for the next following two days. (Today(forecast) + Tomorrow + Day after tomorrow).

The data from the table change accordingly the slide panel above the table. The issue is that I can not find a way with my limited knowledge to change the code for the next days and extract the tables.

I have noticed that the are few changes in the html code when I use the slide panel such as:<g transform="translate(120,0)" - Today forecast<g transform="translate(180,0)" - Tomorrow<g transform="translate(240,0)" - The day after tomorrow.

Also few changes in some div tags and off course Table tag - but still same ID.

However, I have no idea how to link my VBA code with those changes and extract the information needed. My code so far:

Dim HTMLDoc As New HTMLDocument
    Dim objTable As Object
    Dim lRow As Long
    Dim lngTable As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ActRw As Long
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
    objIE.navigate "https://fireweather.niwa.co.nz/region/Otago"
    objIE.Visible = True

    Do Until objIE.readyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    Application.Wait (Now + TimeValue("0:00:06")) 'wait for java script to load
    HTMLDoc.body.innerHTML = objIE.document.body.innerHTML
    With HTMLDoc.body
        Set objTable = .getElementsByTagName("table")
        For lngTable = 0 To objTable.Length - 1
            For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                    ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                Next lngCol
            Next lngRow
            ActRw = ActRw + objTable(lngTable).Rows.Length + 1
        Next lngTable
    End With
    Set objIE = New InternetExplorer
    objIE.navigate "https://fireweather.niwa.co.nz/region/Southland"

    Do Until objIE.readyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    Application.Wait (Now + TimeValue("0:00:03")) 'wait for java script to load
    HTMLDoc.body.innerHTML = objIE.document.body.innerHTML
    With HTMLDoc.body
        Set objTable = .getElementsByTagName("table")
        For lngTable = 0 To objTable.Length - 1
            For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                    ThisWorkbook.Sheets("Sheet1").Cells(ActRw + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                Next lngCol
            Next lngRow
            ActRw = ActRw + objTable(lngTable).Rows.Length + 1
        Next lngTable
    End With
    objIE.Quit
Pmac
  • 21
  • 1
  • All the data comes from one single API call which returns JSON. `https://api.niwa.co.nz/fireweather/publicdata/fire/site/indices?region=otago` Check to see if there is API documentation. You can most likely simply ditch IE, which is no longer supported, and move to using XHR request to the API and use a JSON parser to handle the result e.g. JSONConverter.bas – QHarr Dec 16 '22 at 00:57
  • Hello, @QHarr. Thanks for your time to reply. I was trying to follow your suggestion but i got stucked in understanding what do you mean by XHR request to the API and JSON parser to handle the result e.g. JSONConverter.bas. Could you please breakdown your answers? – Pmac Dec 19 '22 at 18:51
  • 1
    [1](https://stackoverflow.com/a/56493361/6241235) , [2](https://stackoverflow.com/questions/56923231/is-there-a-way-to-slow-down-a-web-scraper-so-it-will-pick-up-the-code/56924071#56924071) , [3](https://scrapecrow.com/reverse-engineering-intro.html) – QHarr Dec 19 '22 at 20:26

0 Answers0