BY CATEGORY▼
VBA CATEGORIES▼
- Excel VBA - Basics (23)
- VBA User Forms (21)
- Excel VBA - pivot tables (8)
- Excel VBA - charts (6)
- VBA - advanced (13)
- VBA - working with files (10)
- VBA - linking applications (9)
- VBA - working with Outlook (13)
- Built-in VBA functions (9)
- VBA - working with data (36)
- VBA - scraping websites (17)
- VBA - working with shapes (3)
- VBA - classes and structures (6)
VBA - SCRAPING WEBSITES VIDEOS▼
- Excel VBA Part 46 - Querying Web Pages with Query Tables
- Excel VBA Part 47.1 - Browsing to Websites and Scraping Web Page
- Excel VBA Part 47.2 - Scraping Website Tables and Clicking Links
- Excel VBA Part 47.3 - Internet Explorer vs XML HTTP Request
- Excel VBA Part 47.4 - Logging in to a Website with Windows Security
- Excel VBA Part 47.5 - Basic HTTP GET and POST Requests
- Excel VBA Part 48 - Scraping Multiple Web Pages
- Excel VBA Part 49 - Downloading Files from Websites
- Excel VBA Part 57.1 - Getting Started with Selenium Basic and Google Chrome
- Excel VBA Part 57.2 - Basic Web Scraping with Selenium and Google Chrome
- Excel VBA Part 57.3 - Using Different Web Browsers in Selenium
- Excel VBA Part 57.4 - Finding Web Elements in Selenium
- Excel VBA Part 57.5 - Implicit and Explicit Waits in Selenium
- Excel VBA Part 57.6 - Working with Multiple Tabs in Selenium
- How do I open Google Chrome in full screen mode using Selenium in VBA?
- Is it possible to start Google Chrome hidden using Selenium in VBA?
- How do I access elements in the Shadow DOM using Selenium in VBA?
VBA - scraping websites videos | Excel VBA Part 47.3 - Internet Explorer vs XML HTTP Request
Posted by Andrew Gould on 03 June 2019
This video shows you how to use VBA to scrape websites using Internet Explorer and XML HTTP Requests. You'll learn how to reference the correct object libraries, how to create an XML HTTP Request and capture the response text. You'll compare the performance of the XML HTTP Request with Internet Explorer by scraping a website of gambling odds and parsing an HTML table, writing the results to a new Excel worksheet.
This video has the following accompanying files:
| File name | Type | Description |
|---|---|---|
| Internet Explorer vs XML HTTP Request.xlsm | Excel workbook with macros |
Click to download a zipped copy of the above files.
Making a video bigger
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 at its bottom right-hand corner.
When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.
Improving the quality of a video
To improve the quality of a video, first click on the Settings icon:
Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.
Choose to change the video quality:
Click on Quality as shown to bring up the submenu.
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 unless you have a fast enough connection speed 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.
Dear Sir -- when running the following code I get the VBA error
>>> Run Time error '91'
>>> Object variable or With block variable not set
Sub ScrapeOddsUsingXMLHTTP()
Dim XMLRequest As New MSXML2.XMLHTTP60
Dim HTMLDoc As MSHTML.HTMLDocument
Dim HTMLDiv As MSHTML.IHTMLElement
Dim HTMLTable As MSHTML.IHTMLElement
XMLRequest.Open "GET", "https://www.oddschecker.com/golf/houston-open/winner", False
XMLRequest.send
If XMLRequest.Status <> 200 Then
MsgBox XMLRequest.Status & " - " & XMLRequest.statusText
Exit Sub
End If
HTMLDoc.body.innerHTML = XMLRequest.responseText ' <== Code breaks down at this point
Set HTMLDiv = HTMLDoc.getElementById("oddsTableContainer")
Set HTMLTable = HTMLDiv.getElementsByTagName("table")(0)
Debug.Print HTMLTable.className
End Sub
Any suggestions? Thank you.
Hi,
You haven't created a new instance of the HTMLDocument class before attempting to change its .body.innerHTML property.
You can do this either by adding this line to your code:
Set HTMLDoc = New MSHTML.HTMLDocument
Or by changing the variable declaration like so:
Dim HTMLDoc As New MSHTML.HTMLDocument
I hope that helps!
Dear Andrew,
Has anything changed in that web-site (https://www.oddschecker.com.....) since you pasted this video?
Because no matter how I try to run the macro using IE or HTTP Request result is the same (slightly different but).
IE gives me following messages:
Can’t reach this page
•Make sure the web address https://www.oddschecker.com is correct
•Search for this site on Bing
•Refresh the page
More information
Error Code: INET_E_DOWNLOAD_FAILURE
Can’t connect securely to this page
This might be because the site uses outdated or unsafe TLS security settings. If this keeps happening, try contacting the website’s owner.
Your TLS security settings aren’t set to the defaults, which could also be causing this error.
Try this:
•Go back to the last page
and HTTP Request shows me that funny msgbox:
Run-time error '-2146697208(800c0008)':
The download of the specified resource has failed.
I tryed to use my code that I wrote watching your lesson or when I ran your code that I downloaded from your web-site.
Is anything wrong with my machine or there's something else I do not understand.
Dear Andrew,
Thatk you so much
Sincerily yours
Alexander
Hi Alexander,
I'm not sure why you're receiving this error message but it seems that you're not the only person to experience it. There are some possible solutions listed in these forums
https://www.tenforums.com/browsers-email/90236-microsoft-edge-tls-security.html
Hopefully one of those will help!