Cannot pull table from different tabs within the same webpage.

M

mj sachoo

New Member
Joined
Nov 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am trying to obtain the data table from
au.investing.com

Australia Stock Market - Investing.com AU

Up-to-date data on the stock market in Australia, including leading stocks, large and small cap stocks.
au.investing.com au.investing.com
website. However, I can only extract the data from the 'Price' tab.

1605753640023.png


I cannot obtain the tables under the 'Performance', 'Technical and 'Fundamental Tabs.

I get the following error when trying to click the other tabs:

1605753837311.png


I do not know how to get these tables from other tabs.

Last hope is this website as I have not got anything from other forums or websites.

Please advise.

Thanks
 
Sort by date Sort by votes
sometimes it loads and sometimes it says "index was outside the bonds of the array".
then It highlighted the code when i clicked on debug.
Click to expand...
The line in error contains a conceptual error, so let's replace the whole Do /Loop cycle with the following code:
Code: 
For I = 1 To 10
    WPage.Wait 400
    Set BColl = WPage.FindElementsByTag("Button")
    If BColl.Count > 0 Then
        If BColl(BColl.Count).Text = "Show more" Then
            BColl(BColl.Count).Click
            WPage.Wait 200
        Else
            Exit For
        End If
    End If
Next I


In addition, I need to let you know the following:

A season is two hours plus
I do change the number in the link to the current season current number
The link we have been using is an already played season which has had all it results for all the matches in the season
I need only an ongoing season data to make my prediction which all the result have not been filled except for the ones that has played.
The yet to be played games scores are denoted with an hyphen "-" attached picture. so I am not sure if that was the reason for the problem
Click to expand...
I don't understand what you are saying, nevertheless let me try for fix the underneath problem: replace, almost at the end of the macro, the existing For I /Next I cycle with the foollowing code:
Code: 
myTab = TbColl(1).AsTable.Data                                      'MARKER 1
For I = 1 To UBound(myTab)
    If Len(myTab(I, 3)) > 4 Then
        myTab(I, 3) = Left(myTab(I, 3), Len(myTab(I, 3)) / 2)
        myTab(I, 4) = Left(myTab(I, 4), Len(myTab(I, 4)) / 2)
    End If
Next I
Range("A1").Resize(UBound(myTab), UBound(myTab, 2)).Value = myTab   'MARKER 2
End Sub
The two lines marked "MARKER" should help identify the position of the code
If this doesn't improve the situation then you have to describe the problem using Excel terminology
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Anthony47 said:
Use tha macro provided in post #22, adding a Stop to set manually the option and removing the worksheet selection (so it will work on the Active sheet):
VBA Code: 
Sub SeleniumSportradarManual()
Dim TbColl As Object, tArr, myItm As Object
Dim I As Long, J As Long, myTim As Single
Dim WPage As Object
Dim myUrl As String
'
'CONFIGURATION:
myUrl = "https://s5.sir.sportradar.com/bet9javirtuals/en/1/season/2403775/standings"     '<<< The web page
''Sheets("Tables").Select                     '<<< The sheet to use    REMOVED, WILL WORK WITH THE ACTIVESHEET
Range("A:T").ClearContents                  '<<< Clear the sheet?
'
myTim = Timer
Set WPage = CreateObject("Selenium.WebDriver")
WPage.Start "Chrome", myUrl                         ' <11
''WPage.Start "edge", myUrl                          ' <22
WPage.Get "/"
'
On Error Resume Next
J = 0
J = Range("A:T").Find(What:="*", After:=Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
J = J + 2

Stop                                                                          '<<< SELECT THE TAB YOU WISH, THEN "CONTINUE" THE MACRO

Debug.Print vbCrLf & "Start", "J= " & J, WPage.Url, Format(Timer - myTim, "0.00")
Set TbColl = WPage.FindElementsByTag("table")
Debug.Print "Tables found: " & TbColl.Count, Format(Timer - myTim, "0.00")
For I = 1 To TbColl.Count
    Cells(J, 2).Value = "Table #" & I
    tArr = TbColl(I).AsTable.Data
    Cells(J + 1, 1).Resize(UBound(tArr), UBound(tArr, 2)).Value = tArr
    Debug.Print "Table #" & I, UBound(tArr) & " * " & UBound(tArr, 2)
    J = J + UBound(tArr) + 3
Next I
'
Debug.Print "END", I, J, Format(Timer - myTim, "0.00")
WPage.Quit
MsgBox ("Collected...")
End Sub

When the macro stops, press in the browser the preferred tab; then "continue" the macro using F5
Click to expand...
Hello Anthony.

I am sorry for writing under this code, I am doing so because it concern this particular code.


I tried to use this code for another section of the website, which is a volleyball stats and has different number of columns from the initial one.

I was able to import all the columns but some cells are converted into something else. e.g let say the cell has "2.3444" instead of "23:25" while some has the repetition of the numbers e.g "23:3523:25" instead of "23:25".

it seams that happened because of the different data type. please kindly help me adjust the code. it will be better if it import them as text.

thanks
 
Upvote 0
These automations require interacting with the web page source code; so which is the url you are analyzing and wich information would you like to get?

Also I'd suggest that we forget about this old discussion and you open a new one, specifying in full your needs, the code you are currently using (maybe a reference to this discussion) and which problems you are facing (so anyone on the forum will be able to help)
 
Upvote 0
Thank you.

This is the new link

Please let do it here since I would have to be referring to this thread again and I also read it that the reason why new thread is advised is for visibility sake.
 
Upvote 0
I was able to import all the columns but some cells are converted into something else. e.g let say the cell has "2.3444" instead of "23:25" while some has the repetition of the numbers e.g "23:3523:25" instead of "23:25
Click to expand...
The quoted macro fails, both using the initial url and the new one; if you have a working one please share it.
Also please explain wich information you would like to extract
 
Upvote 0
Anthony47 said:
The quoted macro fails, both using the initial url and the new one; if you have a working one please share it.
Also please explain wich information you would like to extract
Click to expand...
Hello, I am so sorry for quoting the wrong code. below is the right code:

VBA Code: 
Sub SeleniumSportradarManual()
Dim TbColl As Object, tArr, myItm As Object
Dim I As Long, J As Long, myTim As Single
Dim WPage As Object
Dim myUrl As String
'
'CONFIGURATION:
myUrl = "https://s5.sir.sportradar.com/betradar/en/23/season/109365/fixtures/full"     '<<< The web page
''Sheets("Tables").Select                     '<<< The sheet to use    REMOVED, WILL WORK WITH THE ACTIVESHEET
Range("A:T").ClearContents                  '<<< Clear the sheet?
'
myTim = Timer
Set WPage = CreateObject("Selenium.WebDriver")
WPage.Start "Chrome", myUrl                         ' <11
''WPage.Start "edge", myUrl                          ' <22
WPage.Get "/"
'
On Error Resume Next
J = 0
J = Range("A:T").Find(What:="*", After:=Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
J = J + 2

Stop                                                                          '<<< SELECT THE TAB YOU WISH, THEN "CONTINUE" THE MACRO

Debug.Print vbCrLf & "Start", "J= " & J, WPage.URL, Format(Timer - myTim, "0.00")
Set TbColl = WPage.FindElementsByTag("table")
Debug.Print "Tables found: " & TbColl.Count, Format(Timer - myTim, "0.00")
For I = 1 To TbColl.Count
    Cells(J, 2).Value = "Table #" & I
    tArr = TbColl(I).AsTable.Data
    Cells(J + 1, 1).Resize(UBound(tArr), UBound(tArr, 2)).Value = tArr
    Debug.Print "Table #" & I, UBound(tArr) & " * " & UBound(tArr, 2)
    J = J + UBound(tArr) + 3
Next I
'
Debug.Print "END", I, J, Format(Timer - myTim, "0.00")
WPage.Quit
MsgBox ("Collected...")
End Sub

I want every data on the page, The only problem I have is that the data are being repeated in the cell or converted into a decimal. I think it the data type. I would prefer if all column can be in text format to avoid it.

For more explanation in the Picture below
vba.JPG


1. Cell E6 is meant to be 27:25, so it was written twice as 27:2527:25.
2. Cell E8 is meant to be 22:25, it was converted into decimal which I don't know the basis. same goes for E9.

I wound have prefer it if everything is like No.1 because I can easily extract it.

Thank You.
 
Upvote 0
Ok
1) The "double data" (for example in E6, E7) depends on the web page that indeed has 2 copies of the same data, one of them is "hidden" when read from the browser (see image)
2) The numeric values (for example in E8, E9) depends on the automatic conversion that Excel does on values like hh:mm:ss, if "hh" is less than 24.
So 22:2522:25 is interpreted as 25h+2522m+25s; or 2,66834490740741 in Excel timing notation (in E8)

My choice is to "postprocess" the array tArr with the data for two purposes:
-to halve the data where they look duplicates
-to mark the same data as Text, thus preventing their transformation to timings

The new code:
Code: 
Sub SeleniumSportradarManual2()
Dim TbColl As Object, tArr, myItm As Object
Dim I As Long, J As Long, myTim As Single
Dim WPage As Object
Dim myUrl As String
'
'CONFIGURATION:
myUrl = "https://s5.sir.sportradar.com/betradar/en/23/season/109365/fixtures/full"     '<<< The web page
''Sheets("Tables").Select                     '<<< The sheet to use    REMOVED, WILL WORK WITH THE ACTIVESHEET
Range("A:T").ClearContents                  '<<< Clear the sheet?
'
myTim = Timer
Set WPage = CreateObject("Selenium.WebDriver")
WPage.Start "Chrome", myUrl                         ' <11
''WPage.Start "edge", myUrl                          ' <22
WPage.Get "/"
'
On Error Resume Next
J = 0
J = Range("A:T").Find(What:="*", After:=Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
J = J + 2

Stop                                                                          '<<< SELECT THE TAB YOU WISH, THEN "CONTINUE" THE MACRO

Debug.Print vbCrLf & "Start", "J= " & J, WPage.Url, Format(Timer - myTim, "0.00")
Set TbColl = WPage.FindElementsByTag("table")
Debug.Print "Tables found: " & TbColl.Count, Format(Timer - myTim, "0.00")
For I = 1 To TbColl.Count
    Cells(J, 2).Value = "Table #" & I
    tArr = TbColl(I).AsTable.Data
'>>>
Dim iI As Long, jJ As Long
    For iI = 1 To UBound(tArr)
        For jJ = 1 To UBound(tArr, 2)
            If (Len(tArr(iI, jJ)) Mod 2 = 0) And (Len(tArr(iI, jJ)) > 3) Then
                pospos = InStr(1, tArr(iI, jJ), ":", vbTextCompare)
                If pospos > 0 And pospos < 4 And InStr(3, tArr(iI, jJ), Left(tArr(iI, jJ), 3), vbTextCompare) > 0 Then
                    tArr(iI, jJ) = "'" & Left(tArr(iI, jJ), Len(tArr(iI, jJ)) / 2)
                End If
            End If
        Next jJ
    Next iI
'<<<
    Cells(J + 1, 1).Resize(UBound(tArr), UBound(tArr, 2)).Value = tArr
    Debug.Print "Table #" & I, UBound(tArr) & " * " & UBound(tArr, 2)
    J = J + UBound(tArr) + 3
Next I
'
Debug.Print "END", I, J, Format(Timer - myTim, "0.00")
'WPage.Quit
MsgBox ("Collected...")
WPage.Quit
End Sub
I added the lines in between the markers >>> and <<<

Try...
 
Upvote 0
Anthony47 said:
Ok
1) The "double data" (for example in E6, E7) depends on the web page that indeed has 2 copies of the same data, one of them is "hidden" when read from the browser (see image)
2) The numeric values (for example in E8, E9) depends on the automatic conversion that Excel does on values like hh:mm:ss, if "hh" is less than 24.
So 22:2522:25 is interpreted as 25h+2522m+25s; or 2,66834490740741 in Excel timing notation (in E8)

My choice is to "postprocess" the array tArr with the data for two purposes:
-to halve the data where they look duplicates
-to mark the same data as Text, thus preventing their transformation to timings

The new code:
Code: 
Sub SeleniumSportradarManual2()
Dim TbColl As Object, tArr, myItm As Object
Dim I As Long, J As Long, myTim As Single
Dim WPage As Object
Dim myUrl As String
'
'CONFIGURATION:
myUrl = "https://s5.sir.sportradar.com/betradar/en/23/season/109365/fixtures/full"     '<<< The web page
''Sheets("Tables").Select                     '<<< The sheet to use    REMOVED, WILL WORK WITH THE ACTIVESHEET
Range("A:T").ClearContents                  '<<< Clear the sheet?
'
myTim = Timer
Set WPage = CreateObject("Selenium.WebDriver")
WPage.Start "Chrome", myUrl                         ' <11
''WPage.Start "edge", myUrl                          ' <22
WPage.Get "/"
'
On Error Resume Next
J = 0
J = Range("A:T").Find(What:="*", After:=Range("A1"), _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
On Error GoTo 0
J = J + 2

Stop                                                                          '<<< SELECT THE TAB YOU WISH, THEN "CONTINUE" THE MACRO

Debug.Print vbCrLf & "Start", "J= " & J, WPage.Url, Format(Timer - myTim, "0.00")
Set TbColl = WPage.FindElementsByTag("table")
Debug.Print "Tables found: " & TbColl.Count, Format(Timer - myTim, "0.00")
For I = 1 To TbColl.Count
    Cells(J, 2).Value = "Table #" & I
    tArr = TbColl(I).AsTable.Data
'>>>
Dim iI As Long, jJ As Long
    For iI = 1 To UBound(tArr)
        For jJ = 1 To UBound(tArr, 2)
            If (Len(tArr(iI, jJ)) Mod 2 = 0) And (Len(tArr(iI, jJ)) > 3) Then
                pospos = InStr(1, tArr(iI, jJ), ":", vbTextCompare)
                If pospos > 0 And pospos < 4 And InStr(3, tArr(iI, jJ), Left(tArr(iI, jJ), 3), vbTextCompare) > 0 Then
                    tArr(iI, jJ) = "'" & Left(tArr(iI, jJ), Len(tArr(iI, jJ)) / 2)
                End If
            End If
        Next jJ
    Next iI
'<<<
    Cells(J + 1, 1).Resize(UBound(tArr), UBound(tArr, 2)).Value = tArr
    Debug.Print "Table #" & I, UBound(tArr) & " * " & UBound(tArr, 2)
    J = J + UBound(tArr) + 3
Next I
'
Debug.Print "END", I, J, Format(Timer - myTim, "0.00")
'WPage.Quit
MsgBox ("Collected...")
WPage.Quit
End Sub
I added the lines in between the markers >>> and <<<

Try...
Click to expand...
Hello, It works perfectly. Thank you so much
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA code to extract a table
Replies
5
Views
238
Anthony47
A
Anigito
  • Question Question
Get Webpage Title Text by URL Generated in MS Excel
Replies
1
Views
1K
Anigito
Anigito
B
  • Question Question
Cannot access a table that data is being pulled across from
Replies
1
Views
296
Micron
M
T
  • Solved
=IF Formula Question
Replies
5
Views
485
Fluff
Fluff
B
  • Question Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
539
B-radK
B

Forum statistics

Threads
1,223,996
Messages
6,175,853
Members
452,675
Latest member
duongtruc1610

Share this page

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
Back
Top