How to extract a single span value from a URL behind an iframe Element?

[Indominus]

I am trying to pull a single value from a work website that refreshes every 5 minutes into an Excel cell. This is the final step in automating an entire workbook filled with a bunch of formulas I created. I am using HTTP Get Request to do this. However, I am having problems pulling the actual number I want. It is a span id and it is behind an iFrame element. FlowWidget is the iframe's id. Here is my code I have already. I am new to VBA, so this was a done from a lot of just researching but now I am stuck. Been trying for almost two days now. I have edited it a number of times but with no luck. Now with this one I am getting the error code "object variable or with block variable not set" on the "Set eleNumber" line. Thanks to anyone willing to help. Here is my code.

Sub ScrapInductAmount ()

Dim H As Object, doc As New HTMLDocument, eleNumber As Object

Set H = CreateObject("WinHTTP.WinHTTPRequest.5.1")

URL = "Work URL"

Debug.Print URL
ReTry:

H.SetAutoLogonPolicy 0

H.SetTimeOuts 0, 0, 0, 0

H.Open "GET", URL, False H.send H.WaitForResponse

If H.Status <> 200 Then

MsgBox H.Status & " - " & H.statusText

Exit Sub

End If
Debug.Print H.responseText

doc.Body.innerHTML = H.responseText

Set eleNumber = doc.querySelector("[id='_FlowWidget']").contentDocument.querySelector("span[id=force_induct_count_value]")

Dim inductAmount As String

If Not eleNumber Is Nothing Then inductAmount = eleNumber.innerText

Set doc = Nothing

Range("A1").Value = inductAmount

End Sub

 

[Worf]

Welcome

• If it is a work site, I suppose you cannot share the URL. Can you post the relevant HTML?
• I normally do this with Chrome, see post #4 of the thread below. Your method is good but I would need to test it on the actual page.
• You may need to programmatically enter the frame, see example below.

import data from Web

Dear ALL, I follow your video, I want to import data from Web of this link https://racing.hkjc.com/racing/information/english/Racing/JockeysRides.aspx When I run from web, in explore I read the link, document, does not have TABLE, but I can read table and data, on TABLE VIEW and DATA VIW, when...

www.mrexcel.com

Public d As WebDriver

Public Sub Table_n()
Dim col As Object, i%, col2 As Object, j%, s$, te As Object, re As Object, k%, nc%
Const URL As String = "http://intrinsicedge.blogspot.com/"
Set d = New ChromeDriver
With d
    .Start "Chrome"
    .get URL: .Wait 800
    Set col = d.FindElementsByTag("iframe")
    MsgBox col.Count, , "iframe count"
    For i = 1 To col.Count                                          ' loop iframes
        If col.item(i).Attribute("src") Like "*docs.google*" Then
            s = col.item(i).Attribute("src")
            .Quit
            .get s                                                  ' go to docs page
            Set col2 = .FindElementsByTag("iframe")
            .SwitchToFrame col2.item(1)                             ' enter iframe
            Set col2 = .FindElementsByTag("table")
            MsgBox col2.Count, , "table count"
            Set te = col2.item(1).FindElementsByTag("tr")
            Set re = te.item(10).FindElementsByTag("td")
            nc = re.Count
            For j = 2 To col2.item(1).FindElementsByTag("tr").Count - 1
                For k = 1 To nc
                    Set re = te.item(j).FindElementsByTag("td")
                    Cells(j, k) = re.item(k).Text
                    Set re = re.item(k).FindElementsByTag("a")
                    If re.Count > 0 Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, k), _
                    Address:=re.item(1).Attribute("href"), TextToDisplay:=CStr(Cells(j, k).Value)
                Next
           Next
           Exit Sub
        End If
    Next
    .Quit
End With
End Sub

 

[Indominus]

Welcome

• If it is a work site, I suppose you cannot share the URL. Can you post the relevant HTML?
• I normally do this with Chrome, see post #4 of the thread below. Your method is good but I would need to test it on the actual page.
• You may need to programmatically enter the frame, see example below.

import data from Web

Dear ALL, I follow your video, I want to import data from Web of this link https://racing.hkjc.com/racing/information/english/Racing/JockeysRides.aspx When I run from web, in explore I read the link, document, does not have TABLE, but I can read table and data, on TABLE VIEW and DATA VIW, when...

www.mrexcel.com

Public d As WebDriver

Public Sub Table_n()
Dim col As Object, i%, col2 As Object, j%, s$, te As Object, re As Object, k%, nc%
Const URL As String = "http://intrinsicedge.blogspot.com/"
Set d = New ChromeDriver
With d
    .Start "Chrome"
    .get URL: .Wait 800
    Set col = d.FindElementsByTag("iframe")
    MsgBox col.Count, , "iframe count"
    For i = 1 To col.Count                                          ' loop iframes
        If col.item(i).Attribute("src") Like "*docs.google*" Then
            s = col.item(i).Attribute("src")
            .Quit
            .get s                                                  ' go to docs page
            Set col2 = .FindElementsByTag("iframe")
            .SwitchToFrame col2.item(1)                             ' enter iframe
            Set col2 = .FindElementsByTag("table")
            MsgBox col2.Count, , "table count"
            Set te = col2.item(1).FindElementsByTag("tr")
            Set re = te.item(10).FindElementsByTag("td")
            nc = re.Count
            For j = 2 To col2.item(1).FindElementsByTag("tr").Count - 1
                For k = 1 To nc
                    Set re = te.item(j).FindElementsByTag("td")
                    Cells(j, k) = re.item(k).Text
                    Set re = re.item(k).FindElementsByTag("a")
                    If re.Count > 0 Then ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, k), _
                    Address:=re.item(1).Attribute("href"), TextToDisplay:=CStr(Cells(j, k).Value)
                Next
           Next
           Exit Sub
        End If
    Next
    .Quit
End With
End Sub

Hello. Is there anyway to do this task without downloading something extra. I want to use this task to send the excel file with it around me work so others can use it also.

 

[Worf]

Can you post the relevant HTML?

 

[Indominus]

Can you post the relevant HTML?

Is that my response text?

 

[Worf]

No, it is what you get when you right click the desired page element and choose inspect:

 

[Indominus]

No, it is what you get when you right click the desired page element and choose inspect:

View attachment 18534

I will try to when I get back to work tomorrow