XMLHTTP method

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hi to everybody, I'm using IE, but this method is slow so I want to change it to XLMHTTP.
I have tried but no output.

This is my IE code:

VBA Code:
Sub inplay()

    Dim IE As Object
    Dim item As Object
    Dim r As Integer
    Dim myurl As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Set IE = CreateObject("InternetExplorer.application")
    
    myurl = "https://www.nowgoal.pro/"
  
    With IE
        .navigate myurl
        .Visible = False
  
        Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop
  
    End With
    
    Application.Wait (Now + TimeValue("00:00:10"))

    r = 2

    For Each item In IE.document.getElementsByClassName("item")

        With Sheets("inplay")
            .Range("A" & r).Value = item.getElementsByClassName("gameName leaRow")(0).innerText
            .Range("B" & r).Value = Trim(item.getElementsByClassName("homeTeam").item(0).getElementsByTagName("span").item(0).innerText)
            .Range("C" & r).Value = Trim(item.getElementsByClassName("guestTeam").item(0).getElementsByTagName("span").item(0).innerText)
            'others
        End With

        r = r + 1

    Next item

    IE.Quit
    Set IE = Nothing
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Thanks a lot

Regards
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hi, may I update this discussion?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,269
Office Version
  1. 2010
Platform
  1. Windows
i think the only possible method you will get to work is selenium driver. i have had a look at the way the site works and it is not a simple XMLHTTP target unfortunately
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
i think the only possible method you will get to work is selenium driver. i have had a look at the way the site works and it is not a simple XMLHTTP target unfortunately
I wanted to update the discussion

I inspected the page, here is the html result :

HTML:
A[1]=[1984963,48,28874,3617,'PKNP FC','Negeri Sembilan','2021-03-17 08:50:00','2021-03-17 09:52:27',3,0,2,0,1,0,0,0,1,'8','2',,'True',0,'',64,'',2.75,'True',2,9,1,744,'','','',0];

A[2]=[1969294,7,201,1612,'FC Tokyo','Shonan Bellmare','2021-03-17 09:00:00','2021-03-17 10:03:20',3,2,2,2,1,0,0,0,1,'10','15',1,'True',0.75,'',46,'',2.5,'True',3,2,1,943,'1','','12℃~13℃',1];

B[1]=[31,'SPA D1','Spanish La Liga','#006633',1,'league.aspx?sclassid=31',,'',];

B[2]=[34,'ITA D1','Italian Serie A','#0088FF',1,'league.aspx?sclassid=34',,'',];


VBA Code:
s1 = html.body.innerHTML
p1 = InStr(s1, "=[") + 2
p2 = InStrRev(s1, "]")
s2 = Mid(s1, p1, p2 - p1)
vals = Split(s2, ",")

credits to @John_w


Now, I'am trying to find each array in vals

VBA Code:
    For i = LBound(vals) To UBound(vals)
        Debug.Print vals(0); vals(4); vals(5)
    Next i


What I'm doing wrong?

I appreciate
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Hi, possible to have a light help?

I would like to resolve without disturbing
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
Hello


I am at work right now but will look into this during the weekend.
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126

ADVERTISEMENT

Hi

VBA Code:
Sub sat()
Dim s$, p1, vals, i%, f$
s = "A[1]=[1984963,48,28874,3617,'PKNP FC','Negeri Sembilan','2021-03-17 08:50:00'," & _
"'2021-03-17 09:52:27',3,0,2,0,1,0,0,0,1,'8','2',,'True',0,'',64,'',2.75,'True',2,9,1,744,'','','',0];"
s = s & "A[2]=[1969294,7,201,1612,'FC Tokyo','Shonan Bellmare','2021-03-17 09:00:00'," & _
"'2021-03-17 10:03:20',3,2,2,2,1,0,0,0,1,'10','15',1,'True',0.75,'',46,'',2.5,'True',3,2,1,943,'1','','12?~13?',1];"
s = s & "B[1]=[31,'SPA D1','Spanish La Liga','#006633',1,'league.aspx?sclassid=31',,'',];"
s = s & "B[2]=[34,'ITA D1','Italian Serie A','#0088FF',1,'league.aspx?sclassid=34',,'',];"

p1 = InStr(s, "=[") + 2
vals = Split(Mid(s, p1, InStrRev(s, "]") - p1), ",")
f = ""
For i = 10 To UBound(vals) - (UBound(vals) Mod 10) Step 10
    f = f & "Item " & i & " is " & vals(i) & vbLf
Next
MsgBox f
End Sub
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,269
Office Version
  1. 2010
Platform
  1. Windows
what you will find i believe is that whilst you can process html copied from the site, you will not be able to POST or GET the data via XMLHTTP. also the site is constantly sending updates which require a cookie transaction to occur
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hi

VBA Code:
Sub sat()
Dim s$, p1, vals, i%, f$
s = "A[1]=[1984963,48,28874,3617,'PKNP FC','Negeri Sembilan','2021-03-17 08:50:00'," & _
"'2021-03-17 09:52:27',3,0,2,0,1,0,0,0,1,'8','2',,'True',0,'',64,'',2.75,'True',2,9,1,744,'','','',0];"
s = s & "A[2]=[1969294,7,201,1612,'FC Tokyo','Shonan Bellmare','2021-03-17 09:00:00'," & _
"'2021-03-17 10:03:20',3,2,2,2,1,0,0,0,1,'10','15',1,'True',0.75,'',46,'',2.5,'True',3,2,1,943,'1','','12?~13?',1];"
s = s & "B[1]=[31,'SPA D1','Spanish La Liga','#006633',1,'league.aspx?sclassid=31',,'',];"
s = s & "B[2]=[34,'ITA D1','Italian Serie A','#0088FF',1,'league.aspx?sclassid=34',,'',];"

p1 = InStr(s, "=[") + 2
vals = Split(Mid(s, p1, InStrRev(s, "]") - p1), ",")
f = ""
For i = 10 To UBound(vals) - (UBound(vals) Mod 10) Step 10
    f = f & "Item " & i & " is " & vals(i) & vbLf
Next
MsgBox f
End Sub
Hi Worf, I'm trying your code but I can't adapt it. I think I should opt to parse only strings starting with A = [].
My wish is to have all strings with A [] in each row splitted by the comma.

Using:
VBA Code:
s1 = html.body.innerHTML
p1 = InStr(s1, "=[") + 2
p2 = InStrRev(s1, "]")
s2 = Mid(s1, p1, p2 - p1)
vals = Split(s2, ",")

    For i = LBound(vals) To UBound(vals)
        [B]Debug.Print vals(0); vals(4); vals(5)[/B]
    Next i
I'm able to parse one string

Here as it should be
 

Attachments

  • sat.jpg
    sat.jpg
    141.4 KB · Views: 8

Watch MrExcel Video

Forum statistics

Threads
1,127,524
Messages
5,625,311
Members
416,093
Latest member
tjrogers04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top