Excel VBA Introduction Part 48 - Web Scraping Across Multiple Pages
Why Stack Overflow recommends it
Stack Overflow users link it when answering or following up on VBA web scraping questions, as a worked example of looping over pages with MSXML and HTMLDocument.
Recommended by 3 people across 3 different months, 2017 to 2021.
What it is
A one-hour Excel VBA tutorial from Wise Owl's introductory series showing how to scrape data from many web pages with one procedure. It uses the Microsoft HTML and XML object libraries and covers HTML tags and classes, the Document Object Model and looping over page elements. The presenter is Andrew Gould, and the site used in the demo has since changed its design.
What people said
Recommends Torc · 2017-09 · Stack Overflow · on “Can I automate a website using xpaths in vba?”
It looks like it's possible. My initial reference is to WiseOwlTutorials : [this video] Another reference (more specific to XML / using Xpaths) is from AnalystCave: [LINK] W3Schools looks like it has some good information on syntax as well: [LINK] You will need to go into Tools > References > Select Microsoft XML, v6.0 (or latest) and then set a DOMDocument which will have a collection of nodes. Dim XDoc As MSXML2.DOMDocument60 Set List = XDoc.SelectNodes("//FirstChild/ChildNodes[reference number]/innerText")
Recommends user6432984 · 2017-11 · Stack Overflow · on “VBA web Scraping problems”
This site isn't the easiest to scrape. To save time I iterated over all the divs with class="listing_item span4" and parsed their innerText. I recommend that you watch these videos: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) [LINK] Excel VBA Introduction Part 47 - Browsing to Websites and Scraping a Web Page [LINK] Excel VBA Introduction Part 48 - Web Scraping Across Multiple Pages [this video] [LINK] Sub scrape() Dim URL As String Dim aNext As HTMLAnchorElement, div As HTMLDivElement, doc As HTMLDocument URL = " [LINK] " Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True Do IE.navigate URL Do DoEvents Loop Until IE.readyState = RE
Recommends TinMan · 2020-12 · Stack Overflow · on “I want to automate excel to search and get result from a website”
These resources will help: Excel VBA Introduction Part 46 - Querying Web Pages using Query Tables [LINK] And Excel VBA Introduction Part 47 - Browsing to Websites and Scraping a Web Page [LINK] Excel VBA Introduction Part 48 - Web Scraping Across Multiple Pages [this video]
Cites thsdsdev · 2018-09 · Stack Overflow · on “VBA HTML Scraping Error "Object Variable or With Variable not set"”
I was following a tutorial of this video [this video] made by the WiseOwlTutorials channel and got stuck at a listing procedure he explains at the 36:00 position of the video. At that point, he starts to explain how to return the video url and name of a video list from a specific category through a iteration method called Sub ListVideosOnPage(VidCatName As String, VidCatURL As String) used in another module which loops through all video categories of their website main video page [LINK] (left corner menu list). When this procedure starts, it goes inside each video category and get the name and url of each video from that category in order to list it on a page which, in that part of the Youtube vi
Cites Geo Koro · 2021-10 · Stack Overflow · on “Web Scraping with Login MSXML2.XMLHTTP60”
I'm trying to use a method I saw in this tutorial [this video] to access data in [LINK] However, requires a login. From what I found online it seems very hard to understand how to login to that page. Option Explicit Sub HTMLTest() Dim XMLReq As New MSXML2.XMLHTTP60 Dim HTMLDoc As New MSHTML.HTMLDocument Dim HTMLInputUser As MSHTML.IHTMLElement Dim HTMLInputPass As MSHTML.IHTMLElement Dim HTMLRoles As MSHTML.IHTMLElementCollection Dim HTMLRole As MSHTML.IHTMLElement XMLReq.Open "GET", " [LINK] ", False, "email", "password" XMLReq.send If XMLReq.Status <> 200 Then MsgBox "Problem" & vbNewLine & XMLReq.Status & "-" & XMLReq.statusText Exit Sub End If HTMLDoc.body.innerHTML = XMLReq.responseText Set