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
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.
You can download any files that you need to follow the video here.
You can increase the size of the video:
You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.
You can also increase the quality of the video:
You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.
Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.
Dear Sir -- when running the following code I get the VBA error
>>> Run Time error '91'
>>> Object variable or With block variable not set
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
If XMLRequest.Status <> 200 Then
MsgBox XMLRequest.Status & " - " & XMLRequest.statusText
HTMLDoc.body.innerHTML = XMLRequest.responseText ' <== Code breaks down at this point
Set HTMLDiv = HTMLDoc.getElementById("oddsTableContainer")
Set HTMLTable = HTMLDiv.getElementsByTagName("table")(0)
Any suggestions? Thank you.
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!
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
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.
•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.
Thatk you so much
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
Hopefully one of those will help!