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 (37)
- 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 48 - Scraping Multiple Web Pages
Posted by Andrew Gould on 17 November 2016
PLEASE NOTE - The design of the website used in this video has changed since the video was recorded. This means that the code shown in the video no longer works. The downloadable file contains both the original version of the code and a version which works with the current version of the website. What's better than scraping one web page? Scraping lots of them with the same procedure, of couse! This video explains how to loop over multiple pages using Microsoft's HTML and XML object libraries. You'll learn about HTML tags and classes, the Document Object Model and how to loop over elements on a page.
This video has the following accompanying files:
| File name | Type | Description |
|---|---|---|
| OBSOLETE VERSION - Scraping Multiple Web Pages.xlsm | Excel workbook with macros | |
| REVISED 2018-08-21 -Scraping Multiple Web Pages.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.
I wanted to get each pic url but the code is not executing. After a lot of research i coudn't find the reason. Can you please let me know where i am going wrong?
Const WolVidURL As String = "https://www.coldwellbankerhomes.com/mi/baroda/agents/"
Sub GetVideoPage()
Dim XMLReq As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim VidCats As MSHTML.IHTMLElementCollection
Dim VidCat As MSHTML.IHTMLElement
Dim VidCatList As MSHTML.IHTMLElement
Dim i As Integer
XMLReq.Open "GET", WolVidURL, False
XMLReq.send
If XMLReq.Status <> 200 Then
MsgBox "Problem" & vbNewLine & XMLReq.Status & " - " & XMLReq.StatusText
Exit Sub
End If
HTMLDoc.body.innerHTML = XMLReq.responseText
Set VidCatList = HTMLDoc.getElementsByClassName("split-4 agent-team-results")(0)
Set VidCats = VidCatList.getElementsByTagName("a")
i = 2
For Each VidCat In VidCats
If VidCat.className = "image-wrap" Then
Cells(i, 2) = VidCat.getAttribute("href")
i = i + 1
End If
Next VidCat
End Sub
This is a very interesting and helpful video. Thank you for making this information available to us.
I have one problem at the outset. I downloaded your associated file, but, when I try to run the GetVideoPage macro, I get a runtime error. The code is 8007005, "access denied." The file is not altered in any way. This error appears at the line that reads "XMLReq.send". I have tried the same macro using a different workbook and a different URL with the same result.
I am using Excel 2016 and running it on W10 Pro 64bit.
What, if anything, am I doing wrong?
Hi, I'm almost certain that this is due to us upgrading the website to use https since the video was recorded. If you alter the code so that the URL begins with https instead of just http I believe it will work. I hope that helps!
Thank you. That solved the problem. Everything works perfectly now.