Importing table from Web with multiple pages

[Farrea69]

Hi

I’m working on importing a table from a web however the issue I am having is that the table is divided into pages based on the number of records .

I need my my excel workbook to be able to import all the records into the workbook regardless of the fact they are dividing up into pages with 30 records per page .

is this easily done ?

 

[Domenic]

If you posted the URL, and specified the table you want to import, someone here on the Board should be able to help.

 

[Farrea69]

Here is an example


https://www.transfermarkt.com/fc-arsenal/topTorschuetzen/verein/11

table has 7 pages worth of records , I won’t to include all 7 in Excel

 

[strooman]

The actual page is:
https://www.transfermarkt.com/fc-arsenal/topTorschuetzen/verein/11/ajax/yw1/page/1

Where 1 is the parameter.

There are 7 pages. You could create a variable x for the page number 1 and then cycle through the pages. Something like:

For x = 1 To 7
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "https://www.transfermarkt.com/fc-arsenal/topTorschuetzen/verein/11/ajax/yw1/page/" & x & "", False
        .send
        strHTML = .responseText
    End With
    'Get the HTML
    Set objHTML = New HTMLDocument
    objHTML.body.innerHTML = strHTML
Next

*** UNTESTED ***

 

[Farrea69]

So if the total page count isn’t a constant and is always changing how do I factor that in ?

 

[Farrea69]

Also , I only discovered this was possible this morning in excel so could you direct me to where I can edit the code on the query ?

 

[strooman]

Perhaps not the most elegant solution but it's already late:

<strong>Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).</strong>

1. Copy the below code, by highlighting the code and pressing the keys <strong><span style="color:#FF0000;">CTRL</span></strong> + <strong><span style="color:#FF0000;">C</span></strong>
2. Open your workbook
3. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">F11</span></strong> to open the Visual Basic Editor
4. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">I</span></strong> to activate the Insert menu
5. Press <strong><span style="color:#FF0000;">M</span></strong> to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys <strong><span style="color:#FF0000;">CTRL</span></strong> + <strong><span style="color:#FF0000;">V</span></strong>
7. Press the keys <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">Q</span></strong> to exit the Editor, and return to Excel and make sure you are in an empty worksheet
8. To run the macro, press <strong><span style="color:#FF0000;">ALT</span></strong> + <strong><span style="color:#FF0000;">F8</span></strong> to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Get_That_Data()
'*****************************************************
'Create a reference to:
'Microsoft HTML Object Library
'In Excel press: Alt+F11 | Tools | References
'*****************************************************
Dim objHTML As HTMLDocument
Dim strHTML As String
Dim objItem, tr, td, objRow As Object
Dim r, c, x As Integer

'Counter for the row
r = 1

'We are cycling through 7 pages
For x = 1 To 7
    
    With CreateObject("msxml2.xmlhttp")
        .Open "GET", "https://www.transfermarkt.com/fc-arsenal/topTorschuetzen/verein/11/ajax/yw1/page/" & x & "", False
        .send
        strHTML = .responseText
    End With
    
    Set objHTML = New HTMLDocument
    objHTML.body.innerHTML = strHTML
    
    Set tr = objHTML.getElementsByTagName("tr")
    
    For Each objRow In tr
        'We only need to check the table rows with "odd" and "even"
        If objRow.className = "odd" Or objRow.className = "even" Then
            Set td = objRow.getElementsByTagName("td")
            c = 1
            For Each objItem In td
                Cells(r, c).Value = objItem.innerText
                c = c + 1
            Next
            r = r + 1
        End If
    Next
Next
End Sub

Pay special attention to the comment in the macro:

'***************************************************************
'Create a reference to:
'Microsoft HTML Object Library
'In Excel go to the Visual Basic Editor by pressing: Alt+F11 | Tools | References
'***************************************************************