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 ?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,138
If you posted the URL, and specified the table you want to import, someone here on the Board should be able to help.
 

strooman

Active Member
Joined
Oct 29, 2013
Messages
314
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:

Farrea69

New Member
Joined
Sep 25, 2018
Messages
4
So if the total page count isn’t a constant and is always changing how do I factor that in ?
 

Farrea69

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

Active Member
Joined
Oct 29, 2013
Messages
314
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
'***************************************************************
 

Watch MrExcel Video

Forum statistics

Threads
1,095,348
Messages
5,443,940
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top