Importing table from Web with multiple pages

Farrea69

New Member
Joined
Sep 25, 2018
Messages
4
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 ?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
If you posted the URL, and specified the table you want to import, someone here on the Board should be able to help.
 
Upvote 0
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:

Code:
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 ***
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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.

Code:
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
'***************************************************************
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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