Excel - Efficient Web Scraping in Excel: Extracting Data from Multiple Websites - Episode 665

Microsoft Excel Tutorial: Efficient Web Scraping in Excel: Extracting Data from Multiple Websites.

Welcome back to the MrExcel netcast! In yesterday's episode, I showed you a simple VBA code to extract URLs from a column of links and bring them into a new column. Today, we have a more complex task at hand - scraping webpages and bringing their contents into Excel.

To start off, I will copy the characters from the formula bar of the URL I want to scrape. Then, I will open a new workbook and turn on the macro recorder. After adjusting the settings, I will build the first web query by going to Data, Import External Data, and selecting New Web Query. I will paste the URL into the address bar and click "Go".

Next, I will wait for the webpage to load and then click "Import" and "OK" for the next dialog box. This will bring in all the text from the webpage. I will stop the macro recorder and take a look at the recorded code. By editing the code, I can see where the URL is inserted and how it asks for a specific table if needed.

Now, I will go back to my original workbook where I have already built a loop to go through each cell in a specific range. I will create a variable called "This Url" and set it equal to the URL in the cell. Then, I will paste the recorded code into the loop and change the hard-coded URL to the variable "This Url". This will allow the code to run through each URL in the range and bring in the contents of each webpage.

This method is a quick and efficient way to grab data from a website and bring it into Excel. One of my favorite examples of this is when I had to extract 4,000 addresses from 4,000 different webpages. I wrote an article about it on our website, MrExcel.com/tip072.shtml, where I explain in detail how I built the queries to get all that data. While today's netcast is a simple example with just 10 webpages, this website has a specific example that shows how we can easily and quickly extract a large amount of data from a website.

Thank you for tuning in to this netcast from MrExcel. I hope this video has been helpful and we'll see you next time for more tips and tricks on Excel. Don't forget to like, comment, and subscribe for more content like this. See you soon!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


In todays netcast, I will show you how to use a simple loop and some recorded macro code to pull all of the data from many web pages. Episode 665 shows you how.

Table of Contents:
(00:00) Introduction
(00:18) First step: copying characters from formula bar
(00:40) Using macro recorder
(00:50) Building first web query
(01:05) No table available, grabbing entire web page
(01:20) Editing recorded code
(01:31) Pasting recorded code into initial loop
(02:25) Running the code
(02:45) Results in new worksheets
(02:55) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Automating web page data extraction in Excel
Efficient method for extracting data from websites into Excel
Example of importing multiple web pages into separate worksheets in Excel
Grabbing data from websites and bringing it into Excel
How to extract URLs from a column in Excel using VBA
Importing external data into Excel using web query
Looping through cells in Excel to import web page data
Macro recorder in Excel for web scraping
Using variables in VBA code for web scraping in Excel
VBA code for importing web page data into Excel
Web query in Excel for importing data from websites
YouTube video tutorial on importing web page data into Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...