Extracting table form website and display it in an excel table

Green Squirrel

New Member
Joined
Jan 9, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
This is my 1st post on this website so please be kind :)

I want to pull some table from a website to excel and show them in Excel tables. You might be wondering why I don't use Power Query. Well I'm a Mac user....

I've already managed to pull the table that I want but I just can't figure out how to put this data in an Excel table. Been working on this for days and I just don't succeed.


VBA Code:
Public Sub importMatches()

    Dim QT As QueryTable
    Dim URL As String
        
    URL = "https://www.soccerstats.com/matches.asp?matchday=6"
    
    Set QT = Sheet1.QueryTables.Add( _
                    Connection:="URL;" & URL, _
                    Destination:=Sheet1.Range("B2"))
                     
    With QT
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .WebFormatting = xlNone
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "7"
        .BackgroundQuery = False
        .Refresh
    End With
    
    ' Set a reference to the result range
    Dim qtResultRange As Range
    Set qtResultRange = QT.ResultRange
    
    ' Define the column numbers to delete
    Dim colsToDelete As Variant
    colsToDelete = Array(2, 3, 5, 6, 8, 9, 10)
    
    ' Delete from end to beginning
    Dim counter As Long
    For counter = UBound(colsToDelete) To 0 Step -1
        qtResultRange.Columns(colsToDelete(counter)).EntireColumn.Delete
    Next counter

End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,749
Welcome to MrExcel forums.

To convert the data to a table you must first delete the query table, since a query result range can't occupy/overlap the same cells as a table, and then convert the query result range to a table.

Try this:
VBA Code:
Public Sub ImportMatches()

    Dim destCell As Range
    Dim QT As QueryTable
    Dim qtResultRange As Range
    Dim URL As String
        
    URL = "https://www.soccerstats.com/matches.asp?matchday=6"
    
    With Sheet1
        Set destCell = .Range("B2")
    End With
    
    Set QT = destCell.Worksheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=destCell)
    With QT
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .WebFormatting = xlNone
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "7"
        .BackgroundQuery = False
        .Refresh
        ' Set a reference to the result range
        Set qtResultRange = .ResultRange
        .Delete 'must delete QueryTable to convert its result range to a table
    End With
    
    ' Define the column numbers to delete
    Dim colsToDelete As Variant
    colsToDelete = Array(2, 3, 5, 6, 8, 9, 10)
    
    ' Delete from end to beginning
    Dim counter As Long
    For counter = UBound(colsToDelete) To 0 Step -1
        qtResultRange.Columns(colsToDelete(counter)).EntireColumn.Delete
    Next counter

    'Convert data range to a table
    
    With destCell
        .Worksheet.ListObjects.Add xlSrcRange, .CurrentRegion, , xlYes
    End With

End Sub
 

Green Squirrel

New Member
Joined
Jan 9, 2021
Messages
25
Office Version
  1. 365
Platform
  1. MacOS
Hi John,

Thank you very much. But now I can't update the table anymore? Any idea?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,749
With the changes below you can now run the macro again to refresh the query and update the table.
VBA Code:
Public Sub ImportMatches()

    Dim destCell As Range
    Dim QT As QueryTable
    Dim qtResultRange As Range
    Dim URL As String
        
    URL = "https://www.soccerstats.com/matches.asp?matchday=6"
    
    With Sheet1
        Set destCell = .Range("B2")
        On Error Resume Next
        'Delete webData table if it already exists
        .ListObjects("webData").Delete
        On Error GoTo 0
    End With
    
    Set QT = destCell.Worksheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=destCell)
    With QT
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .WebFormatting = xlNone
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "7"
        .BackgroundQuery = False
        .Refresh
        ' Set a reference to the result range
        Set qtResultRange = .ResultRange
        .Delete 'must delete QueryTable to convert its result range to a table
    End With
    
    ' Define the column numbers to delete
    Dim colsToDelete As Variant
    colsToDelete = Array(2, 3, 5, 6, 8, 9, 10)
    
    ' Delete from end to beginning
    Dim counter As Long
    For counter = UBound(colsToDelete) To 0 Step -1
        qtResultRange.Columns(colsToDelete(counter)).EntireColumn.Delete
    Next counter

    'Delete the note at bottom of data
    
    qtResultRange.Item(qtResultRange.Rows.Count, 1).ClearContents
    
    'Convert data range to a table named webData
    
    With destCell
        .Worksheet.ListObjects.Add(xlSrcRange, .CurrentRegion, , xlYes).Name = "webData"
    End With

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

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
Top