ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
The Easiest Way to Scrape Web Data with VBA
ฝัง
- เผยแพร่เมื่อ 28 มี.ค. 2022
- Learn how to write real-world Excel VBA code: 👉courses.excelm...
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 (excelmacromast...)
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)
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 TH-cam 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?
Really. You are the best VBA code professor I know. As you keep sharing we keep improving. Thanks for it.
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!
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!
Brilliant Paul !!! Thanks !! I've been using the same for years but glad there's an accessible reference to it on TH-cam now !!
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!!
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!
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
Excellent explanation. You have made this task much less daunting. 👏.
Very practical and helpful video with very detailed explanation!
Very helpful examples, thank you for the video!
Glad it was helpful karl!
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
Thanks Paul. This video is very helpful. You should be canonized for your body of work.
Best Excel channel on youtube
Thanks Daniel
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
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
Amazing video Paul!🥳🍀😁🤟😎
Thanks John, Glad you like it.
Excellent tutorial, makes it possible to explore many fronts.
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
Mind-blowing... Best Web scrapping with VBA video ❤❤❤
Thanks a lot N'rele. Glad you liked it.
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.
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
Great video, thanks for sharing. Another tool in the toolkit.
Thanks Andre!
Thank you very much for sharing valuable information.
As always... well done Paul. Thanks very much.
Thanks Joao
I love this video! Thanks for sharing this info.
Awsome! This is the object used by powerquery when loaded in a table
thank you so much for this tutorial, so helpful and very informative. watching from abu dhabi uae
Glad you like it.
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...
Great job, thanks for sharing this helpful way.
Hi, if web site is protecting like How to add username and password entering code?
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
Great one! Thanks Paul.
You're welcome Wayne.
Great one! Would it be possible to populate the data as well (on web pages) using this approach?
👍 Amazing!!
I can’t seem to get the code files, could you please post them again?
This is so fast, amazing. How would you fetch urls from a table instead of a hard coded url?
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 .
Awesome tutorial. Thanks a lot.
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.
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
Perfect. Very useful.
Best web scraping video
Thanks Michel.
sir how can we achieve IMPRTXML FUNCTIONALITY of google sheet into MS excel ?
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?
"needs to review the security of your connection before proceeding"
hi, I got this message when running the vba code you're providing
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.
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...
Great video! How do I write the code if I need to scrap the table from mulitple web pages?
Thank you, nice video!!
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.
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?
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.
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?
Would be great if you could do a video on scraping an e-commerce site
Great video.
great video - just wondering if this method be used to scrape a table ?
thank you very helpful video.
Glad it was helpful!
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.
Nice coding example, although for this job Power Query suits better
Debatable:)
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
It give me error massage : unable to open "url". Cannot download the information requested.
how would I edit this code to just get one element from the webpage? preferably by ID
thank you
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.
Hi. Excelent
is it posible insert into MS ACCESS Table ?
Can we download excel report from a website ??
Awsome !! Thanks for sharing !!!
Thanks for watching!
Nice video but seems to be totally useless when table data is displayed from a PHP query or script which in my experience is where most website data tables comes from.
hello, How can i interact or populate a webpage form through VBA MSXML without opening IE or without IE method. pls kindly help?
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?
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
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?
There is a QUERY function in google sheets. Any such option in Excel (including VBA)?
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?
Can you please suggest how to send data to web page and click on button.
Is there a way to scrape data from multiple salary pdf payslips into excel table ?
If there are multiple tables on the website then how can I extract let say 1-5 tables ??
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.
I'm new to VBA & thought the task look easy but still not find the way despite google.
I want to achieve following through VBA code :
i've following prepared:
Column A- list of Urls
Column B - download folder ( location on computer)
Column C- status of downloaded file ,i.e. Ok,error,etc(required after completing following process)
1.open the url from a list of urls ;
2.find a specific button-"Excel" on the webpage opened in step 1;
3.Click that button, a new window will pop up, asking the location to save the data in excel format;
4.press save button to save the file;
5.Open next url as per step 1 & repeat above process till all the files from specified urls are saved in specified folder.
can u help on above..??
Does this work for dynamic web pages?
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?
Excellent
is working for 100 pages??or just one page???
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"
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
helo sir, how scrape data from registeried page ?
I still use vba to send bulk email at once with all attachment in every mail....my team mates are non technical...they are always shocked how I do work so fast...my bosses are mad at me for using automation since they want me to be donkey...doing manual work.....but I am a smart datamonkey ...
The link for the source code doesn't seem to work. Getting this error: "This site can’t be reached"
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, 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
Rather than delete and replace the equal sign, you can also press Ctrl+space to see intellisense.
lol! True - I use this so automatically that I actually forgot about it.
@@Excelmacromastery The only reason I remember is that I spent a couple months using Python and had to consciously remember not to use Tab.
What if there is an authentication to the website we are trying to access. How to handle that using query tables ?
You would need to use one of the other libraries such as Selenium or Http XML.
perfect bro
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 ;-)
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.
There is actually one more way of scraping a web (and other applications also) in VBA.
The UI Automation Client. Very powerful, very complex and hard to learn, but wow when you get it...
Then you can do almost anything.
Sounds great Mikael. Thanks for mentioning it. I'll definitely take a look.
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?
I’m stuck with f2a even I knew all credentials. Any Idea sir