Import Website Table to excel

[samzie83]

Hi, I am attempting to import the below website table into excel with great difficulty.

https://www.flashscore.com/nhl/standings/

I believe the Standing table which I am trying to import is a JavaScript table.

What is the easiest way (beside manually copying and pasting every time) to get these details onto excel?

Regards,

Sam

 

[Domenic]

The following code will first create a new worksheet within the workbook running the code, and then copy the table into the newly created worksheet. The code should be placed in a regular module. Note that you'll need to set a reference to Microsoft Internet Controls, and Microsoft HTML Object Library, under VBE > Tools > References.

Option Explicit

Sub test()


    'Set a reference (VBE > Tools > References) to the following libraries:
    '   1) Microsoft Internet Controls
    '   2) Microsoft HTML Object Library
    
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim HTMLTable As MSHTML.HTMLTable
    Dim HTMLRow As MSHTML.HTMLTableRow
    Dim wksDest As Worksheet
    Dim i As Long
    Dim j As Long
    
    With IE
        .Visible = False
        .navigate "https://www.flashscore.com/nhl/standings/"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Set HTMLDoc = IE.document
    
    Set HTMLTable = HTMLDoc.getElementById("table-type-1")
    
    If Not HTMLTable Is Nothing Then
        Set wksDest = ThisWorkbook.Worksheets.Add
        For i = 0 To HTMLTable.Rows.Length - 1
            For j = 0 To HTMLTable.Rows(i).Cells.Length - 1
                wksDest.Cells(i + 1, j + 1).Value = HTMLTable.Rows(i).Cells(j).innerText
            Next j
        Next i
    Else
        MsgBox "Table not found!", vbExclamation
    End If
    
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Set HTMLTable = Nothing
    Set HTMLRow = Nothing
    Set wksDest = Nothing
    
End Sub

Hope this helps!

 

[samzie83]

Thanks for the quick response Domenic. Are you able to give an example of setting a reference for Microsoft Internet Controls and HTML Object Libary. I am a little unsure how this works.

 

[Domenic]

Okay, let's take it step by step...

1) Open the Visual Basic Editor by pressing and holding down the ALT button, and then pressing the F11 button.

2) Under the menu in the Visual Basic Editor, first select Tools, and then select References.

3) Under Available References, scroll down until you see Microsoft Internet Controls, and then check/select it.

4) Again, under Available References, scroll down until you see Microsoft HTML Object Library, and then check/select it.

5) Click on OK, and then save your workbook.

Then you'll be able to run the code by going to the View tab in Excel and selecting Macros or by using the keyboard shortcut ALT + F8, and then selecting the sub, and then clicking on Run. By the way, in the code itself, you can change the name of the sub from 'test' to something more meaningful. For example, if you want the sub to be called DownloadStanding, you can replace...

Sub test()

with

Sub DownloadStanding()

Hope this helps!