No kidding. I've been watching videos on VBA web scraping since yesterday and was going through UA-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!
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.
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!!
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
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!
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!
@@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…
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
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
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
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?
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.
Hello, I used the code with multiple pages and it successfully imported the data. But there is a secure page link for student data and the tables are secure. When I place the link, it does not import the data. Is there a solution for this?
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
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 .
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
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?
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...
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.
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, 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...
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
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
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"
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 ;)
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?
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.
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?
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)
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 😊
@@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
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
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.
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?
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..??
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
Hello my teacher I watched your youtube videos and wanted to consult you on a difficult matter. We want to export the websites and e-mail addresses of the brands we find on the Internet into Excel. So on the left row is the company or brand name, right next to it is the website and email automatically retrieved from google. As we find new brands we will add them to the excel file and as the code runs it will continue to find their website and email. I would be very grateful if you could help me with this, free or paid. Enjoy your job.
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.
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 UA-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?
The most useful thing I've seen this year... man... you rock!
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!
Brilliant Paul !!! Thanks !! I've been using the same for years but glad there's an accessible reference to it on UA-cam now !!
This video and code is going to save me countless hours on a research project. Thank you so much!!!
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 practical and helpful video with very detailed explanation!
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!!
Best Excel channel on youtube
Thanks Daniel
Very helpful examples, thank you for the video!
Glad it was helpful karl!
Excellent explanation. You have made this task much less daunting. 👏.
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
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.
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.
Hi, if web site is protecting like How to add username and password entering code?
I love this video! Thanks for sharing this info.
Mind-blowing... Best Web scrapping with VBA video ❤❤❤
Thanks a lot N'rele. Glad you liked it.
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
Thanks Paul. This video is very helpful. You should be canonized for your body of work.
thank you so much for this tutorial, so helpful and very informative. watching from abu dhabi uae
Glad you like it.
As always... well done Paul. Thanks very much.
Thanks Joao
Thank you very much for sharing valuable information.
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
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.
Very interesting video!! One question: if the website needs userid and password how I can modify the macro? Thank you.
Great video, thanks for sharing. Another tool in the toolkit.
Thanks Andre!
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?
how would I edit this code to just get one element from the webpage? preferably by ID
thank you
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.
Is there a way to scrape data from multiple salary pdf payslips into excel table ?
Hello , love your code, but seem to run into a problem now then selenium couldn’t support chrome version 128, how can I resolve this, many thanks
hello, How can i interact or populate a webpage form through VBA MSXML without opening IE or without IE method. pls kindly help?
Great one! Thanks Paul.
You're welcome Wayne.
I can’t seem to get the code files, could you please post them again?
Hi. Excelent
is it posible insert into MS ACCESS Table ?
Hello, I used the code with multiple pages and it successfully imported the data. But there is a secure page link for student data and the tables are secure. When I place the link, it does not import the data. Is there a solution for this?
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.
Great video! How do I write the code if I need to scrap the table from mulitple web pages?
If there are multiple tables on the website then how can I extract let say 1-5 tables ??
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.
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?
Does this work for dynamic web pages?
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 .
This is so fast, amazing. How would you fetch urls from a table instead of a hard coded url?
It give me error massage : unable to open "url". Cannot download the information requested.
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
👍 Amazing!!
Can you please suggest how to send data to web page and click on button.
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?
There is a QUERY function in google sheets. Any such option in Excel (including VBA)?
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...
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.
great video - just wondering if this method be used to scrape a table ?
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?
sir how can we achieve IMPRTXML FUNCTIONALITY of google sheet into MS excel ?
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, 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...
Perfect. Very useful.
Can we download excel report from a website ??
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
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
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
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"
Awsome! This is the object used by powerquery when loaded in a table
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?
Awesome tutorial. Thanks a lot.
Great one! Would it be possible to populate the data as well (on web pages) using this approach?
No, you can't do that.
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
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?
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.
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?
Great video.
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.
Best web scraping video
Thanks Michel.
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.
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.
The link for the source code doesn't seem to work. Getting this error: "This site can’t be reached"
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)
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
Would be great if you could do a video on scraping an e-commerce site
is working for 100 pages??or just one page???
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 ;-)
Awsome !! Thanks for sharing !!!
Thanks for watching!
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.
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 very helpful video.
Glad it was helpful!
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..??
helo sir, how scrape data from registeried page ?
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
I’m stuck with f2a even I knew all credentials. Any Idea sir
When I click the code
link it just sits there forever not downloading anything
Sounds like you have a popular blocker running. Turn it off and then try.
Hello my teacher
I watched your youtube videos and wanted to consult you on a difficult matter.
We want to export the websites and e-mail addresses of the brands we find on the Internet into Excel. So on the left row is the company or brand name, right next to it is the website and email automatically retrieved from google.
As we find new brands we will add them to the excel file and as the code runs it will continue to find their website and email.
I would be very grateful if you could help me with this, free or paid.
Enjoy your job.
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.