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
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
VBA Code:
For i = LBound(vals) To UBound(vals)
        Debug.Print vals(0); vals(4); vals(5)
Next

You are hard coding the indexes 0, 4 and 5. Inside the loop you have to use the “i” index.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
VBA Code:
For i = LBound(vals) To UBound(vals)
        Debug.Print vals(0); vals(4); vals(5)
Next

You are hard coding the indexes 0, 4 and 5. Inside the loop you have to use the “i” index.
Hi, I tried with
VBA Code:
ind = UBound(vals) - LBound(vals)
For i = 1 To ind
Cells(i, 1) = Trim(vals(i))
Next i

My result is:


36​
13149​
364​
Sao Jose PoA RS'
Gremio (RS)'
2021-03-22 23:00:00'
2021-03-22 23:00:00'
2​
0​
0​
0​
0​
0​
0​
1​
1​
10'
4'
True'
-1​
'
39​
'
2.75​
True'

I'd like each row starting with A [] = to be on single row, like here https://www.mrexcel.com/board/attachments/sat-jpg.34918/

It can be done?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
I saved the JS file as TXT and imported it into Excel, getting the following result. What would you think of this method?

bf_us.js2.txt
ABCDEFGH
1var A=Array(322);
2var B=Array(106);
3var C=Array(56);
4var matchcount=321;
5var sclasscount=105;
6A[1]=[196263217109264967'Formartine United''Annan Athletic''2021-03-23 19:45:00''2021-03-23 19:45:00'
7A[2]=[196264017146774'Greenock Morton''Dunfermline Athletic''2021-03-23 19:45:00''2021-03-23 19:45:00'
8A[3]=[1984181235034469'Boavista F.C''Fluminense RJ''2021-03-23 21:00:00''2021-03-23 22:05:08'
9A[4]=[19857486941761976'Sport Club Recife PE''Confianca SE''2021-03-23 22:00:00''2021-03-23 22:01:11'
10A[5]=[199401097863428617'San Francisco FC''Plaza Amador''2021-03-23 22:00:00''2021-03-23 22:00:00'
11A[6]=[19662761626532649'Boyaca Chico''Envigado FC''2021-03-23 23:00:00''2021-03-23 23:00:00'
12A[7]=[19941444832723297'Columbus Crew''New York City Football Club''2021-03-23 23:00:00''2021-03-23 23:00:00'
13A[8]=[1964573272899840510'Tlaxcala FC''Pumas Tabasco''2021-03-23 23:00:00''2021-03-23 23:00:00'
14A[9]=[1984105247429349'Mirassol''Corinthians Paulista (SP)''2021-03-24 00:00:00''2021-03-24 00:00:00'
15A[10]=[1991113984243824000'CD Independiente Juniors''Pottu Via Ho''2021-03-24 00:00:00''2021-03-24 00:00:00'
16A[11]=[19857496920012096'Fortaleza''Santa Cruz PE''2021-03-24 00:30:00''2021-03-24 00:30:00'
17A[12]=[19857506998441979'Centro Sportivo Alagoano''Bahia''2021-03-24 00:30:00''2021-03-24 00:30:00'
18A[13]=[19662801626442206'Independiente Santa Fe''Deportiva Once Caldas''2021-03-24 01:00:00''2021-03-24 01:00:00'
19A[14]=[1964574271007817382'Venados FC''Celaya FC''2021-03-24 01:05:00''2021-03-24 01:05:00'
20A[15]=[19903413028324790'Gimnasia La Plata<font color=#880000>(N)</font>''Sood G''2021-03-24 01:10:00''2021-03-24 01:10:00'
bf_us.js2
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
I saved the JS file as TXT and imported it into Excel, getting the following result. What would you think of this method?

bf_us.js2.txt
ABCDEFGH
1var A=Array(322);
2var B=Array(106);
3var C=Array(56);
4var matchcount=321;
5var sclasscount=105;
6A[1]=[196263217109264967'Formartine United''Annan Athletic''2021-03-23 19:45:00''2021-03-23 19:45:00'
7A[2]=[196264017146774'Greenock Morton''Dunfermline Athletic''2021-03-23 19:45:00''2021-03-23 19:45:00'
8A[3]=[1984181235034469'Boavista F.C''Fluminense RJ''2021-03-23 21:00:00''2021-03-23 22:05:08'
9A[4]=[19857486941761976'Sport Club Recife PE''Confianca SE''2021-03-23 22:00:00''2021-03-23 22:01:11'
10A[5]=[199401097863428617'San Francisco FC''Plaza Amador''2021-03-23 22:00:00''2021-03-23 22:00:00'
11A[6]=[19662761626532649'Boyaca Chico''Envigado FC''2021-03-23 23:00:00''2021-03-23 23:00:00'
12A[7]=[19941444832723297'Columbus Crew''New York City Football Club''2021-03-23 23:00:00''2021-03-23 23:00:00'
13A[8]=[1964573272899840510'Tlaxcala FC''Pumas Tabasco''2021-03-23 23:00:00''2021-03-23 23:00:00'
14A[9]=[1984105247429349'Mirassol''Corinthians Paulista (SP)''2021-03-24 00:00:00''2021-03-24 00:00:00'
15A[10]=[1991113984243824000'CD Independiente Juniors''Pottu Via Ho''2021-03-24 00:00:00''2021-03-24 00:00:00'
16A[11]=[19857496920012096'Fortaleza''Santa Cruz PE''2021-03-24 00:30:00''2021-03-24 00:30:00'
17A[12]=[19857506998441979'Centro Sportivo Alagoano''Bahia''2021-03-24 00:30:00''2021-03-24 00:30:00'
18A[13]=[19662801626442206'Independiente Santa Fe''Deportiva Once Caldas''2021-03-24 01:00:00''2021-03-24 01:00:00'
19A[14]=[1964574271007817382'Venados FC''Celaya FC''2021-03-24 01:05:00''2021-03-24 01:05:00'
20A[15]=[19903413028324790'Gimnasia La Plata<font color=#880000>(N)</font>''Sood G''2021-03-24 01:10:00''2021-03-24 01:10:00'
bf_us.js2
Hi, saving and importing are automatic? That js page is constantly updated, so I don't know, I accept any method if it's as fast as possible.
 

Worf

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

ADVERTISEMENT

I will try to automate the method, which I did manually.

I am having a busy workweek but will get back to you as soon as possible.
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
I understand, this is a hobby for me, so take your time.
I recap what I need:

On that page there are live events, this is a single event:

A[1]=[1885625,5,2454,2457,'Volgar-Gazprom Astrachan','Dinamo Briansk','2021-03-24 14:30:00','2021-03-24 15:33:26',3,2,1,1,1,0,0,4,2,'11','19',,'True',1.25,'',18,'',2.25,'True',8,2,1,674,'1','','-1℃~0℃',1];
B[5]=[235,'RUS D1','Russian National Football League','#8d5abf',1,'subleague.aspx?sclassid=235',674,'',];

1885625 = ID event
5= ID league that is in B[5] array

The sheet should look like this:

1885625​
Russian National Football LeagueVolgar-Gazprom AstrachanDinamo Briansk
24/03/2021 14:30​
24/03/2021 15:33​
21

If matching the ID league is complicated, it can be discarded.

I prefer to understand what I'm doing, for this reason I ask you for some ideas to start trying.

Thank you
 

Worf

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

ADVERTISEMENT

This works for me:

VBA Code:
Public Sub GetInfo()
Dim d As WebDriver, fn$
Set d = New ChromeDriver
Const URL = "http://www.nowgoal.pro/gf/data/bf_us.js"
With d
    .Start "Chrome"
    .get URL
    fn = "C:\test\" & "web page " & Format(Date, " yyyy-mm-dd") & ".txt"
    Open fn For Output As #1
    Print #1, .FindElementByTag("body").Text    ' create text file
    Close #1
    '.Quit
End With
Application.CutCopyMode = False
' bring data into Excel
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fn, Destination:=[a1170])
    .name = "bf_us.js2_3"
    .FieldNames = True
    .PreserveFormatting = True
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePlatform = 65001
    .TextFileParseType = xlDelimited
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = 0
    .TextFileCommaDelimiter = 1
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
End Sub
 
Solution

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Thank you Worf, how can I autorun this power query with other macros?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
This is not Power Query; PQ could be a good option if the data were in JSON format.

I do not understand what you want to do. This code can be called by other routines and can be triggered by Excel events.

Please explain what is your idea.
 

Bruzio

Board Regular
Joined
Aug 20, 2020
Messages
85
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
Hi Worf, I tried Power Query, works good with txt conversion.
I thought your last code was a test to save the txt since you used selenium, so I tried Power Query directly.

Thanks for your time, the discussion is resolved.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,982
Messages
5,621,964
Members
415,869
Latest member
LWSkinner

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