Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Posted by Andrew Gould on 07 May 2019
In this video you'll learn how to write VBA code to click links on a webpage to view different tables and then scrape the contents of those tables into new Excel worksheets.
See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.
This video has the following accompanying files:
File name | Type | Description |
---|---|---|
Scrape Football Site.xlsm | Excel workbook with macros |
Click to download a zipped copy of the above files.
There are no exercises for this video.
You can increase the size of your video to make it fill the screen like this:
Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown above.
When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.
To improve the quality of a video, first click on the Settings icon:
Make sure yoiu're playing your video so that the icons shown above appear, then click on this gear icon.
Choose to change the video quality:
Click as above to change your video quality.
The higher the number you choose, the better will be your video quality (but the slower the connection speed):
Don't choose the HD option shown unless your connection speed is fast enough to support it!
Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.
From: | Mahesh |
When: | 24 Apr 20 at 12:17 |
Hi Experts,
Following your videos i was able to login to website via VBA, but i'm now stuck in to get the table info to Excel.
My site has Expand/Collapse options for tables here. i'm stuck can any one help? If i click expand I see a table, where it's that table info that i need to import into Excel. How can i do this kindly advise.
TIA
From: | Andy B |
When: | 24 Apr 20 at 12:40 |
Right-click on the webpage to view its source. Have a look at whether the tables which are initially hidden appear in this source. If they don't, they're being populated probably in the client script, and you're unlikely to be able to get at them. If they do, have a look at the structure and you should be able to scrape their contents.
From: | Mono |
When: | 25 Mar 20 at 16:16 |
Sub myrecon()
Dim myhtml_element As IHTMLElement
Dim myurl As String
On Error GoTo err_clear
myurl = " https://webpage"
Set mybrowser = New InternetExplorer
mybrowser.Silent = True
mybrowser.navigate myurl
mybrowser.Visible = True
Do While mybrowser.readyState <> READYSTATE_COMPLETE Or mybrowser.Busy
Set htmldoc = mybrowser.document
htmldoc.all.username.Value = "name"
htmldoc.all.password.Value = "pass"
For Each myhtml_element In htmldoc.getElementsByTagName("button")
If myhtml_element.Type = "submit" And myhtml_element.class = "button" Then myhtml_element.Click:
Exit For
Next
Do While mybrowser.readyState <> READYSTATE_COMPLETE Or mybrowser.Bus
For Each myhtml_element In htmldoc.getElementsByTagscr("/arm/skins/images/structure/matte/card//square/reconciliations.png")
If myhtml_element.Title = "Reconciliations" And myhtml_element.alt = "Reconciliations" Then myhtml_element.Click:
Exit For
Next
err_clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If
Hello,
Thank you so much for sharing all the information, I created a macro to navigate trough a websit but the 2nd loop is not working, would you please help me?
Thanks again.
From: | Andy B |
When: | 26 Mar 20 at 16:28 |
We're a bit distracted at the moment at the owlery by other events, but maybe someone else will be able to suggest a solution?
From: | mkayiran |
When: | 03 Jan 20 at 00:59 |
Hello Andrew Gould & WiseOwlTutorials
Thank you very much for bringing these videos together! It really helped me practice the sample I wanted to do! I was able to gather the information I wanted using I.E. However, when I tried to simulate my actions when using the MSXML2.ServerXMLhttp.6.0 method, VBA was unable to recognize the same ID and tag names recognized by I.E.
I present my VBA code at the bottom. Can you help with this?! Where's the error?
Thank you so much. Good work.
My VBA Code:
Option Explicit
Sub ListTableOptions()
Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim TableOptionsLinks As MSHTML.IHTMLElementCollection
Dim TableOptions As MSHTML.IHTMLElement
Dim TableOptionLink As MSHTML.IHTMLElement
Dim TableName As String
Dim URL As String
Dim NextHref As String
Dim NextURL As String
Dim DisplayName As String
DeleteOldSheets
URL = "https://www.whoscored.com/Regions/108/Tournaments/5/Seasons/7928/Stages/17835/Show/Italy-Serie-A-2019-2020"
XMLPage.Open "GET", URL, False
XMLPage.send
If XMLPage.Status <> 200 Then
MsgBox "Problem" & vbNewLine & XMLPage.Status & " - " & XMLPage.statusText
Exit Sub
End If
HTMLDoc.body.innerHTML = XMLPage.responseText
Set XMLPage = Nothing
Set TableOptions = HTMLDoc.getElementById("tournament-tables-17835-options")
Set TableOptionsLinks = TableOptions.getElementsByTagName("a")
For Each TableOptionLink In TableOptionsLinks
If LCase(TableOptionLink.innerText) <> "progress" Then
TableName = Right(TableOptionLink.href, Len(TableOptionLink.href) - InStr(TableOptionLink.href, "#"))
NextHref = TableOptionLink.getAttribute("href")
NextURL = URL & Mid(NextHref, InStr(NextHref, ":") + 6)
XMLPage.Open "GET", NextURL, False
XMLPage.send
If XMLPage.Status <> 200 Then
MsgBox "Problem" & vbNewLine & XMLPage.Status & " - " & XMLPage.statusText
Exit Sub
End If
HTMLDoc.body.innerHTML = XMLPage.responseText
Set XMLPage = Nothing
ProcessTable HTMLDoc.getElementById(TableName & "-grid"), TableOptionLink.innerText, TableName
End If
Next TableOptionLink
End Sub
Sub ProcessTable(HTMLTable As MSHTML.IHTMLElement, DisplayName As String, TableName As String)
Dim HTMLTable1 As MSHTML.IHTMLElement
Dim TableSection As MSHTML.IHTMLElement
Dim TableRows As MSHTML.IHTMLElementCollection
Dim TableRow As MSHTML.IHTMLElement
Dim TableCell As MSHTML.IHTMLElement
Dim A1 As MSHTML.IHTMLElement
Dim A2 As MSHTML.IHTMLElement
Dim RowNum As Long, ColNum As Integer
Worksheets.Add
ActiveSheet.Name = DisplayName
Range("A1").Value = DisplayName
RowNum = 2
For Each TableSection In HTMLTable.Children
If LCase(TableSection.tagName) <> "tfoot" Then
If LCase(TableSection.tagName) = "thead" Then
Set TableRows = TableSection.getElementsByTagName(TableName & "-general-header")
ElseIf LCase(TableSection.tagName) = "tbody" Then
Set TableRows = TableSection.Children
End If
For Each TableRow In TableRows
ColNum = 1
For Each TableCell In TableRow.Children
Cells(RowNum, ColNum).Value = TableCell.innerText
ColNum = ColNum + 1
Next TableCell
RowNum = RowNum + 1
Next TableRow
End If
Next TableSection
Range("A1").CurrentRegion.EntireColumn.AutoFit
Range("A1:B1").Font.Size = 12
Range("A2", Range("A2").End(xlToRight)).Offset(-1, 0).Interior.Color = rgbDarkBlue
Range("A2", Range("A2").End(xlToRight)).Interior.Color = rgbCornflowerBlue
Range("A2", Range("A2").End(xlToRight).Offset(-1, 0)).Font.Color = rgbWhite
Range("A2", Range("A2").End(xlToRight).Offset(-1, 0)).Font.Bold = True
End Sub
Sub DeleteOldSheets()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If Not ws Is Menu Then ws.Delete
Next ws
Application.DisplayAlerts = True
End Sub
From: | Luis |
When: | 27 Jun 19 at 20:06 |
Hi Andrew
Thank you to share your knowledge
I try to text the code of video webscrapin nº 47.2 with the method of request get insted of Aplication Internet explorer and it dosent work at all (only capture the value of the tables section head and foot but the body dosent capture any value)
Do you know why is it?
Thanks
From: | Andrew G |
When: | 01 Jul 19 at 09:40 |
Hi Luis,
The most likely reason for this is that the website has removed the information for that particular tournament - this is one of the main problems with making examples for current events: it's inevitably out-of-date very quickly!
You can find lots of other examples of the same type of page on the same website such as this one https://www.oddschecker.com/golf/open-championship/2019-open-championship/winner which I've just tested and works in the same way as the example shown in the video.
I hope that helps!
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.