Odd HTML Scrape Problem

[Themilkybarkid]

Hi all,

I am trying to parse a table from a website. I have used similar code successfully in the past but when using it to scrape this particular website it returns some odd data. Having inspected the web page the data I want is located within the td tags as expected but I can't figure out why the VBA code isn't reading it properly.

All help and feedback welcome

Many thanks



Sub RetrieveData()

Dim myurl As String
Dim TDElement As Object
Dim TDElements As IHTMLElementCollection
Dim IE As MSXML2.XMLHTTP60
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLBody As MSHTML.HTMLBody
Dim k As Long
Dim j As Long
Dim l As Long
Dim sht As Worksheet


ThisWorkbook.Sheets("Sheet1").Range("B3:L300").ClearContents


Set IE = New MSXML2.XMLHTTP60
Set HTMLDoc = New MSHTML.HTMLDocument
Set HTMLBody = HTMLDoc.body

myurl = "Market Data | EPEX SPOT"
IE.Open "GET", myurl, False
IE.send

On Error Resume Next


HTMLDoc.body.innerHTML = IE.responseText
Flat = False
k = 0
j = 3

Set TDElements = HTMLDoc.getElementsByTagName("td")
For Each TDElement In TDElements
ThisWorkbook.Sheets("Sheet1").Cells(j, k + 2) = TDElement.innerText
k = k + 1
If k = 7 Then j = j + 1
If k = 7 Then k = 0
Next


End Sub

 

[CSmith]

Not sure but seemed to work just fine for me as you had it coded. What do you mean by 'not reading it properly'?

 

[daverunt]

I am looking at that at the moment. I can't confirm this is the OPs problem but check the Excel Column H figures from the bottom up compared to the actual table values.
Is it me or do some of those figures not match...

 

[CSmith]

I am looking at that at the moment. I can't confirm this is the OPs problem but check the Excel Column H figures from the bottom up compared to the actual table values.
Is it me or do some of those figures not match...

My testing showed k=27 to be the number where data lined up in proper columns

 

[CSmith]

if you want stacked then k=9

 

[daverunt]

K=9 sorted the problem I saw when trying the code.

Maybe the OP will report back....

 

[Themilkybarkid]

hi guys, thanks for all the replies. K=9 has appeared to help with the formatting.

What I can't get my head around is once you run the code it doesn't then seem to update correctly as you re-run it. It returns spurious figures that are similar but not the exact values shown in the table. It's almost as if once you run the code once, there is a block on being able to scrape the page again. As the website updates live I can't show you what I mean but if you wait 5 minutes or so between running the code a second time and look at the web page you should see discrepancies between it and what the VBA returns you.