The Easiest Way to Scrape Web Data with VBA
Vložit
- čas přidán 28. 03. 2022
- Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
Want to download the source code for this video? Go here: bit.ly/3NrfGt4
Subscribe to the channel here: bit.ly/36hpTCY
The Easiest Way to Scrape Web Data with VBA
In this video I show you how to scrape data using a little-known method. The method is powerful because it only requires a few lines of code and doesn't require any installation or browser.
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard) - Věda a technologie
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
Want to download the source code for this video? Go here: bit.ly/3NrfGt4
how to fetch data from sql
How can you handle say PAGINATED web data? same using VBA?
But what if the page is not table elements, only divs and labels?
Hello sir, can one also load data on the html pages using the field ID (without relying on the sendkeys method). Any inputs will be valuable.
No kidding. I've been watching videos on VBA web scraping since yesterday and was going through CZcams literally right now looking for more getting ready for a project. Then you post this new video in the middle of that. Thank you!
That's Great Brandon.
@@Excelmacromastery Say, while I got your attention, do you know if it is feasable to capture a whole website and all its assets to make a copy of it?
Another AMAZING video! Thanks very much for showing how easy it can be to scrape data from web pages. The solution presented is simple and elegant and easily adapted to a wide variety of use cases. Thank you!!
Brilliant Paul !!! Thanks !! I've been using the same for years but glad there's an accessible reference to it on CZcams now !!
Really. You are the best VBA code professor I know. As you keep sharing we keep improving. Thanks for it.
Very good video and annotations. In about only 7 minutes I've learned more then in an hour of searching and reading.
Many thanks for that!
This was great.
I have been watching videos all day and this is the first one that worked.
I look forward to watching this same video using other methods besides tables.
Awesome, thank you!
This video and code is going to save me countless hours on a research project. Thank you so much!!!
The most useful thing I've seen this year... man... you rock!
Excellent explanation. You have made this task much less daunting. 👏.
Great video with lots of insights, which is really the norm for Paul at this point.
Query tables look really powerful, maybe you could make a video more in detail about it in the future. I'm sure many, myself included, would find that very helpful.
Thank you!
Thanks Alexandru
Excellent tutorial, makes it possible to explore many fronts.
Great video, well presented as always.
As a long time excel user , I suspected this code was effectively the same as using the legacy data wizards
(Data: GetData: Legacy Wizards: From Web. Note you may need to enable wizards: Excel Options: Data: Show legacy data import wizards: From Web)
In some instances, I have found the legacy data connections are a lot faster than the modern PowerQuery methods
Anyway, if you turn on the VBA recorder and then use the above wizard, you will get the same core VBA code generated as per the video
However, in my quick performance check, the PQ method was substantially faster for this case
Thanks Alan. Good info.
Ahhh. This is excellent but I got so hooked to Power Automate for web scraping as it makes it sooo easy. Still, this tutorial is A+. Going to attempt this to have further hands on knowledge on vba
Great job, thanks for sharing this helpful way.
I love this video! Thanks for sharing this info.
Very helpful examples, thank you for the video!
Glad it was helpful karl!
Very practical and helpful video with very detailed explanation!
As always... well done Paul. Thanks very much.
Thanks Joao
Mind-blowing... Best Web scrapping with VBA video ❤❤❤
Thanks a lot N'rele. Glad you liked it.
Great one! Would it be possible to populate the data as well (on web pages) using this approach?
THIS!!! IS!!! AWESOME!!! I was looking for a way to let a workbook pull down data from a MySQL database on the web easily. I think with the right bit of code on my server I can make this happen with these tools.
THANK YOU SO MUCH!
Have you tried using PowerQuery?
@@jvdb5509 no. I really am no Excel wizard. I’m an old school programmer trying to automate some stuff for a good friend and our ski race league. I’ve developed a bit of familiarity with VBA and this idea seems to get me around some other areas I have no familiarity with… LOL ….as well as not requiring total control over the server…
You're welcome
Awesome tutorial. Thanks a lot.
Amazing video Paul!🥳🍀😁🤟😎
Thanks John, Glad you like it.
Perfect. Very useful.
Thank you very much for sharing valuable information.
Best Excel channel on youtube
Thanks Daniel
Great video! Would I be able to pull out all terms on a webpage that were bolded and list them on a column with this same code?
Great video, thanks for sharing. Another tool in the toolkit.
Thanks Andre!
👍 Amazing!!
Thank you, nice video!!
You're doing great job, Paul, thank you! One question: is it possible to scrape Web Data with VBA the exact way as follows?
I define about hundred URLs and also three to five keywords/phrases to find on those websites. VBA does the search job and when found, it returns the results in a spreadsheet as links to the articles that content those keywords/phrases. The process would be repeated once a week. Is it doable with the VBA?
thank you so much for this tutorial, so helpful and very informative. watching from abu dhabi uae
Glad you like it.
Great one! Thanks Paul.
You're welcome Wayne.
Great, Paul. Thanks for this. I want to ask you two questions. How send user & password to a site, and how populate the search box with a input box or a cell content.Regards
Wanna know as well
You're showing the chrome examples saying don't have to use all this code, I'm thinking that looks neat as, already. Superb information as usual. Do you have a video on reading larger csv files 250k rows but managing memory and cleaning up afterwards. I will look as well .
great video - just wondering if this method be used to scrape a table ?
Awsome! This is the object used by powerquery when loaded in a table
Tnak you for the Video, awesome! When I go to download stock quotes I only get updated data about once a minute, the sites seem to somehow prevent a second by second update. How would this still be possible?
Great video.
Great video! How do I write the code if I need to scrap the table from mulitple web pages?
This is so fast, amazing. How would you fetch urls from a table instead of a hard coded url?
Awsome !! Thanks for sharing !!!
Thanks for watching!
FIrst of all a very nice and useful tutorial. I have a practical question: I use the querytable approach. Unfortunately the webpage Table uses another number format then my Excel (. and , swapped for dezimals). Is it possible to change this in the querytable?
Can this method be used to navigate to other pages using links and to enter information needed, like login and passwords? If so, have you done a video on it? If so, may I get a link to the video? Thank you.
as always presented in a clear, easy and reassuring way :) How do you know which table reference (table 1) to use? Is it simply counting them visually or is there somewhere on the web page that you have to go to see that the table is referenced as table 1?
No clear cut way. You can count them on the page but it's not always obvious what is a table. You can check the underlying html code. The easiest thing is to run the code until you hit the correct one although this may not always be feasible.
Thanks Paul. This video is very helpful. You should be canonized for your body of work.
Best web scraping video
Thanks Michel.
Excellent
thank you very helpful video.
Glad it was helpful!
perfect bro
this video is amazing, I used to use httprequest, how about if the website required login name and pw, is it possible use "query table"? thank you
Could this method be used to grab a JWT (*Jason Web Token) from an Azure Database for use in running a Single Sign On process?
Hi, if web site is protecting like How to add username and password entering code?
Hello, thank you for the video. I need help, please
What should we do?
If the site requires you to log in first, then scrape the data from it after that
Is there a way to use the whole page code, but using a large list of URLs and placing their data into new sheets? 1 URL = 1 new sheet.
I need to do this for a large amount of URLs and can't figure out how to do it in batches/more than one at a time.
Hi. Excelent
is it posible insert into MS ACCESS Table ?
always a pleasure
Hello, good evening, wow, excellent tutorial. I have tried the macros and they work wonderfully, but something strange happens to me and I have not been able to find what is happening.... I perform a scraping to a web whose unique table has more or less 15000 objects but it only reflects me in the sheet only 100 .... What could be happening? I hope you help me understand thank you very much...
Does this work for dynamic web pages?
how would I edit this code to just get one element from the webpage? preferably by ID
thank you
Is there a way to scrape data from multiple salary pdf payslips into excel table ?
Super!
Thank you very much!
There is a QUERY function in google sheets. Any such option in Excel (including VBA)?
Hi i just have a quastion about some help in my excel to automate a few steps, it's any chance to contant you ? Like discord or something else
Hello, can you make a video to get unique values fastest way?
- Dictionary way
- Collection way
- Unique function from 365 and 2021
and other methods that i don't know etc...
I am working on a vba to fill web form, it works on first IE page and submit the form, but when a new page loads with different web address, my code don't recognize the active web page... Any advice??? No selenium used
If there are multiple tables on the website then how can I extract let say 1-5 tables ??
Can you please suggest how to send data to web page and click on button.
I am trying to download the Query Table source code but I get stuck on an infinite loading screen. Is there a better way to get around this? As it stands right now the code generates an error saying "Incomplete Database"
In case login requires captcha code, it is very difficult to pass this code, is there a way to scrape data from the website that is already logged in? Thank you very much
Nice Video! But I get the response that I have to Enable javascript from the webpage I try to scrape? Is there any solution for this?
hello, How can i interact or populate a webpage form through VBA MSXML without opening IE or without IE method. pls kindly help?
Incredible! Thank you. When I call up Yahoo! historical stock data, the date parameters are set to today's date and then back one year. Is there a way to set the date parameters differently?
It may be possible if the parameters are in the url. otherwise you'll need to use either Chrome Selenium or XMLHttp.
Would be great if you could do a video on scraping an e-commerce site
Excellent work. One question, is there a way to make the code wait for the query to complete before proceeding? Using that "Main" sub you used on the video as an example, if I have code after the UseQueryTable to do something with the values that I expect from the table it will try to do so before the table is there because the query takes a while. I tried using a Sleep function after the UseQueryTable call but it seems to also halt the progress of query....
Had the same issue with macro which refreshed tables in Excel (connected to Oracle database through PowerQuery) and later used those tables in some calculations. I've tried Sleep, Wait but it didn't fit my needs. Finally I've stumbled upon DoEvents and it worked like a charm ;)
@@pjetrucha how could I use doevents in this case to allow the query to complete?
For those who are fairly new to coding, what you see there in the VBA coding window is the development environment that all Microsoft centric coders worked in twenty years ago 😊
Don't be ridiculous Visual Studio has been introduced in 2000 and it was by far the best ide in the world.
@@FilipCordas actually, the first version of VS came out in '97. I used that version extensively back then, and each iteration since then. But the IDE experience back then was very similar to this - hence my comment
@@jim2lane I still have a copy of Visual Basic 1.0 - those were the days. Bleeding edge programming and boy were we bleeding at times
I can’t seem to get the code files, could you please post them again?
Can we download excel report from a website ??
Hello, I am Omar from Egypt. I am interested in your videos. I would like to ask a question. Can I access a data source for a website.
When I search about some on website
Anyone know if this can be used on a webpage with a username and password? IE, not public, but it's my account that I log into.
This is great, but how do you login and submit your username and password before you access the data. I can enter my username and password, but can't figure out how to click on the button as it doesn't seem to be clearly identifiable in the HTML.
Hi, I want to scrap Indimart Data. There are no options for exporting the data. I want the leads available for the specific category. I have the API as well. Please can you guide me on this
QUESTION .... I scrape 10,00+ webpages so this will really help. BUT if a URL is embedded in text on a webpage, PQ or BI won't extract the URL eg email address is embedded in the person's name. I've searched and can't find it. Any ideas?
Hello, excellent video, but i have a question, what if we need to get data from a webpage that isn't in a table and we need to find the html location? Can you make a video explaining that webscraping method please? how to extract specific data from a webpage
Look at 5:42 there is some info but it needs some learning ;-)
is working for 100 pages??or just one page???
I have a question - how I can we scrape using your method, in case if I need scrape multiple tables from the different pages?
You would use the AfterRefresh event to detect when the QueryTable Refresh was complete(see docs.microsoft.com/en-us/office/vba/api/excel.querytable.afterrefresh)
It give me error massage : unable to open "url". Cannot download the information requested.
You able to do webscrape, however, scraping data from the page filters that I normally have to MANUALLY populate the search criteria? For example, I have to get just simple total numbers of times a particular person’s username processed certain items for the day.
I have to do this for about 10 different users from my team at work. And I have to pull the data by manually populating that person’s username in the specific search filter field criteria then the page clocks and spits out the data table, then I manually highlight the data on the page in order to get a simple total number of times that come up for that specific username for the day. And I have to type and change the username on a specific filter search space every time for all of my work group members that I’m collecting the data.
Also, depending on how long it takes the page to get the data, I know there’s a VBA feature that prevents the VBA from “timing out” in case the data at times takes longer depending on the size the data table.
sir how can we achieve IMPRTXML FUNCTIONALITY of google sheet into MS excel ?
Did you test Selenium recently, though? I can't make it work. Probably because the library wasn't updated since 2016... I wrote a lot of automations for IE and I am looking for a replacement (since IE is going to be discontinued soon). Your method is cool, but my automations are focused on executing scripts, clicking buttons and inputting data into boxes.
Selenium should work fine for scraping how are you running it headless or browser? Have you checked Power Automate you can do a lot using the desktop version?
@@FilipCordas For me Selenium fails at the driver.Start command. I tried with both Edge and Chrome. Yes, Power Automate is one solution, Python is another. Not having an ability to install whatever you want on your work laptop due to company policy is a problem, however.
@@Malignvs What version of Selenium driver do you have? Python is not a solution it's a programming language a bad one at that, but I find it strange that they would allow you to install python sdk, ide and selenium drivers for python rather than power automate that comes native in windows 11 that's the reason I recommend it you might have it already, but it could be the case corporate IT is usually ridiculous.
Nice coding example, although for this job Power Query suits better
Debatable:)
The link for the source code doesn't seem to work. Getting this error: "This site can’t be reached"
Promising but after putting this in and running, it is just so very slow to pole the site and write the data. Not sure anything can be done to speed up. I'm using Windows 10 and Office 365.
When you delete the table doesn't that breaks all the formulas that were using it ?
Yes. If you want to simply update a table you can use Refresh once the table is created.
I’m stuck with f2a even I knew all credentials. Any Idea sir
Hello, compliments, I would like to ask you if you think it is possible to create a vba for Google search. I would need to enter a barcode on the Excel sheet and through the search of the code it should return me product name, brand, description, and image link. Is something like this possible? It would be great if you could make a video for it. I think it would be interesting for all traders like me who have a physical store and an online site and each time uploading products to the site is boring because it involves thousands of product sheets, so if we could manage to search through the barcode product on the web and returns: product name, description, brand, and 3-4 product image links in an excel sheet to then upload the sheet to the site. Would be the best. Let me know if this idea of mine is possible or impossible, thank you
"needs to review the security of your connection before proceeding"
hi, I got this message when running the vba code you're providing
helo sir, how scrape data from registeried page ?
It’s definitely better than the old IE method. But seriously, use Python not VBA for webscraping
Maybe for extensive web scraping. But if you just need to get data from a page or table then installing and learning Python is a big overhead for most people.