Excel VBA Introduction Part 46 - Querying Web Pages using Query Tables
ฝัง
- เผยแพร่เมื่อ 24 ก.ย. 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
Download files here www.wiseowl.co...
www.wiseowl.co.uk - Querying web pages in Excel VBA is simple when you use Query Tables. This video shows you how to create a query table linked to a web page, how to choose which tables from the web page to return, and how to pass values to a URL query string to create dynamic, updatable web queries in your Excel workbooks.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
You are the best teacher I have ever met! You are simply fantastic. It is an honor for me to be able to learn from you. Thank you!
its biggest charity to share knowledge, may god bless u
Andrew, I rarely comment on TH-cam, but you are by far the best teacher anywhere. Your enthusiasm and dedication is contagious. Slightly off topic, but your generosity to put this much content online is giving me hope in humanity again
I found this site is one of the best educational source. Teaching skill are awesome ! great !
This is too awesome. I jumped ahead to see where the course was headed and landed on this video. Simple awesome. When I compare this to the steps it takes to do the same thing in Python, I'm just blown away. Thanks.
God...I finsihed this video in one time...almost no need to rewind to understand. The other guy's video, I watch a week and haven't finished one hour video...Thank you. I like yours...not just a little better than others...
Happy to hear that! Thanks for taking the time to leave a comment and for watching!
i consider myself quite knowledgable on Excel and VBA. If been using Excel since version 5. I genuinely learned something new.
Clear explanation. Good pace. Lovely voice.
You really explained very well since you explain the concept first and then the solution, this technic needs to be applied by every teacher. Keep it up
The most perfect explanation ever heard! Always to the point!!
You are best teacher i have meet online... thanks alot
Thanks wiseowl> I will be donating. This is an amazing course. Thank you for putting the time and the book is excellent.
Thank you Cyrus! We all appreciate your support!
very helpful and quite love the accent!
It is outstanding! I am in the middle of learning your VBA course, I think for the students who need to learn the Excel VBA, your videos are the must to watch , your excellent teaching styles makes the course extremely educating and entertaining.
One thing I would like to ask you is: are there any VBA courses related to the importing live data from certain trading platform console to the excel and use certain modeling to determine the trading strategy, for example, if I am using IB (Interactive Broker or Etrade) to import the live data to excel, and then plot these live data minute by minute, such as moving average, RSI and so on, then use these information to determine buy or sell, any courses related to these topics?
Again, thank you so much.
Very well explained. Thank you
Just what I was looking for! Subbed
Awesome tutorial !!!
Thank you very much, Andrew.
The videos of this channel are outstanding!!!
Congrats,
Regards
Simply Awsome.
Thank you Faisal!
Outstanding video..Thanks again
I Was looking this for Ages! Thank you Sir very good explanation and tutorials.
Great video as always, thank you for sharing
reallly fantastic , if i found every thing i need in the wise owl cources , i will not use onother source to learn my vba cources
Thank you Mahdjoub, I hope you find everything you're looking for!
@@WiseOwlTutorials many thanks teacher
Great video! Thanks Andrew!
Very, very nice!!! This helps me a lot. I want to do it with a website that has a login system.
I'll play around, see what I come up with. Thanks as always my friend...
Excellent!! Looking forward to watching it...
Thanks!!
You're awesome Andrew
:D thanks Yasser!
Interesting tutorial, but works only on certain pages. Cool way to download data anyway.
Dear Andrew, thanks for this video. You really make our lives so easier.
I'm an ardent follower of your channel and have learned a lot by just tuning into your channel.
I see that the video on automating web works wonderfully well. I have tried many websites and found it working without any hassles. However the same code isn't working when I try to implement in my office. I mean these codes don't work for internal websites of my organization. I'm getting automation error(unspecified error)/ disconnection error (the object invoked has disconnected from its clients) when I try to run the same code.
Is there a special reason or if there is any other way round?
Thanks Andrew. Enjoyed.
Great tutorial. I followed it easily and it all worked first time, which is pretty rare for TH-cam videos. I have a question please. When using the sites you suggest it works great. When I try on another site it doesn't return any data. The other site clearly has tables on but looking at the source code I cant see any table tags. What's the best way to get data out of those types of web tables ? Thanks.
Great stuff again. Thank you very much.
Thank you for the detailed video
Thanks for sharing a knowledge
Hi Wiseowl, Thank you so much for the videos. I wish you ran courses on Lynda. Is there any form of certificate you provide for completing your course? I have a more general question Can VBA use APIs? I am trying to get some specific data from a website which comes in JSON or XML format.
i think i used to request this technique in other video. Anyway thanks you for this video.
These videos are amazingly helpful. Thank you.
Is it possible to target specific tables using their ID or CLASS attributes, or even CAPTION tag, rather than using an index number? Similarly, can you specify the amount of rows/columns, or start/end points, of a table to import?
How can I change the format of the query numbers. In my excel the expressions in thousands are used the points "." and the decimals in "," but the page has the number formats inversely
thanks for sharing the such kind of video it’s really help
I am facing one issue I am able to find the element but when I try to click it is not working and not showing any errors could plz help me
Thanks @wiseowltutorials .. your videos are so helpful .. do you have any videos on extracting PDF file content using VBA without adobe acrobat DC Pro version?
Hi sorry no we don't have any videos on that topic.
getting a an error that is subscript out of range for Set qt= wsrates.querytables("Xrates") please help me in this
Hi Anrew, thank you for one of your 1° class lessons as always well explaned rich of various arguments and example. I have a short answer, as you know google run very fast in this world and is look like the need of more knowlegess is high. Do you think you'll make some vidos about google spreadsheets how to program some scripts? Actually i would learn how to translate all the macros i've learnt from you from VBA to Javascript. I woud apreciate it very much and i'm sure not just me ;) Thank's 1s more to share ur knowleges. From me always a thumb high!!!
Thank you 4 ur fast reply Andy, don't think about that, doesn't matter, i understand that u r busy 120%, i just let you know that what u r doing ist the best what the Net can offer, and i'm sure, the day you'll start to make videos around JS, they were be the best explained as ever and i will lern all what i wish learn. Thank you ones more...keep going on...
you can make some video about Progamming with the Windows API
Hi Thanks for the help! Just wanted to know is there any way that we can get all the data from the html file in excel.. daily the html link is different. How can we do that. Instead of mentioning only one website.
hi sir and frinds this working very good
but every time it creats a new sheet
how to over write data on the same sheet on each time execute this macro
excel 2021 gives me an error when trying to Refresh the tables, do you have any solution?
Hey my friend Andrew. A question from your biggest Fan. When i run exactly the code up to the time 12:36 in the video (and really exact, not missing a single line), it happens a message error to me that says "RunTimeError '5', argument or calling invalid procedure ". Or something like that translated to engish... Can you help me with that. I would love. Great hughs from Brazil.
Hi Andrew, I have the same problem (Runtime error 5). I uses exactly the same code as you, and changing the URL to https does not work. any idea how to fix this?
I found out what the problem is. You need to run this code from within a Module, and not from within a Microsoft Excel Object (Sheet1 for example, thats what I did).
i also have the same problem.it is showing run time error. i have tried solutions provided here but its still not working.
You must put your code in a module, that will fix the issue.
Хоть я в английском не понимаю, но по видео и по коду vba все становится понятно.)) Единственный вопрос, а как на 19:20 вашего видео, прописать в vba, чтобы вытащить имеено все строки таблицы в интервале от table до table?
Hi Wise Owl,
very well explained i liked it.
I have created a for loop and taking a stock price from google but there it shows previsous stocks price in loop, so how to clear xml page or htmldoc page.
please help me out in this
Thanks...
Amazing! Thank you so much! :)
Excellent video! Do you have any/know of any videos that show to login into a password protected website (username and password)? I was attempting to follow along with the video, but the website that I need to be able to get data from is password protected.
Any guidance would be greatly appreciated. Thanks!!
Férlyn Reyes i also have the same requirement..
Very helpful! Thank you :D
i have test the update xrate....when i click update button there is an error message : " This Web query returned no data"
How do you the wsrates sheet? I dont understand that
Running into a snag with the loop. It runs on the firt and second sheets and then hangs on the 3rd. Any suggestions? When I debug, it highlights the line with .Refresh just above the End With.
how to fetch data(7.0654) below? they seem not be able to read using "innertext"7.0654
How do you know how many variables you need to create for a specific task?
Can we make refresh dynamic, I mean if I want to take action after refresh so my next code should not execute till complete refresh is done. I want to put exact time with seconds and date when query is refreshed. I don't want to put static Application.wait code here. Appreciate if you could help around this?
I'm using Excel 2016, why is it that when i click the "from web" a different dialog box appear that is asking for the URL either in basic or advanced. Is it the same thing because I'm having a hard time using it because of the slow loading, how can make that new web query dialog box to appear?
Good formulass thanks for sharing
I am working on a project to change hotel rates on different webpages but problem is ie.elementsby Id is causing object required issue even if element is there.When i run macro manually with F8, it works fine but its causing that issue when i run it automatic with clicking f5
Do you have any advise ?
Thanks for your helps,
Lovely day,
Cihan
then you don't have a long enough page load wait. Use a time loop testing for presence of element
Fantantisco video
Hi Andrew. I have just 1 query which 1 should a user prefer Query Table or Selenium? Because I find query table very easy to learn, understand and don't even need to refer any library unlike Selenium.
Hi Yash! If you can get the answer you want using a QueryTable then go with that option!
@@WiseOwlTutorials Okay, but whatever videos you have done for can selenium can we do the same work by suing query table as well?
@@yashsomaiya8141 Hi Yash! No, Query Tables allow you to connect to HTML tables but that's all.
@@WiseOwlTutorials okay
Web page have multiple select element (dropdow combo) select upper value than other value populat depend upon first value help me how do I enter value on all combo dynamic
Sir, pls explain more on
sub UpdateXRates
Set qt = wsRates.QueryTables("XRates")
my Question is
how it this code will work on new sheet
it is showing error, when i use following code line
Set qt = wsRates.QueryTables(1)
Pls help
Thanks for this video Andrew !
I have a query table which gets data from a google sheet. Also i have a couple of modules which need to execute as soon as there is a change in the query table.
I m using "change" event to get the modules run as soon as there is a change in the sheet and the table refreshes every one minute.
Is there any way that macros execute after refresing of the query table or preferably only when it detects a change in the query table after refresh? My pc is slow.
Hi Daniel! There is a QueryTable.AfterRefresh event but you'll need to do some work with a class module to get this working. Here's some code to help stackoverflow.com/questions/18136069/excel-vba-querytable-afterrefresh-function-not-being-called-after-refresh-comp
@@WiseOwlTutorials i appreciate your reply. ❤
With IE 11 no longer supported, do you know if MS is changing Excel to work with Edge?
Hi Ricardo! I don't think that Microsoft has any plans to provide a VBA library for working with Edge. There are several unofficial projects which allow you to do this however - we have a set of videos on working with the SeleniumBasic library which allows you to work with Edge, Chrome, Opera and others. You can start with video 57.1 in this playlist if you're interested th-cam.com/play/PLNIs-AWhQzcl3xKvF8sVL4sWRWICj_clM.html
execution of the line beginning with set qt shows an error message stating "Invalid procedure call or argument"
Hello, thnx for the great work
13:33 on the video, about the refresh, what if i want to refresh not automatically after 5min or when file open.. i want to refresh via a button.. how can i do it?
Thnx for your help,
(Notice ; im a beginner, my first day in vba and things, so my question)
If i put this on the code instead of the function that you putted .refreshfileopen.. does it work directly? What should i modifie if not ?
Sub RefreshAllQueryTables()
Dim qt As QueryTable
For Each qt In ActiveSheet.QueryTables
qt.Refresh
Next qt
End Sub
@@elyy3077 Hi! Yes, you can attach that subroutine to a button on the same worksheet as your query tables and they will be refreshed when you click the button.
thank you i always love your video if i want to contact you what
i have to do ? i have business proposal
Hi
I cannot query webpages not supported by IE browser plz help
Do you have any free Advance Excel video tutorial? If yes then, Please share link.
when i run a Sub some time in actisheet, in range("a1").,many tables have been created...I want only table and just update data...help me.
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & url, Destination:=Range("a1"))
With qt
' .RefreshOnFileOpen = True
.Name = "vinh"
.WebFormatting = xlWebFormattingRTF
.WebSelectionType = xlAllTables
.Refresh
End With
Can anybody please help - My query tables work just fine when it comes to the process of extracting data from the web but unfortunately it doesn't paste all the data. So for example i have 145 rows in Google Sheets and QueryTables only paste 100.
What is wrong ?
Hi im using Excel O365 MAC and im having issues finding the power query... MSFT support couldnt help me. does anyone here have the same problem ?
Hi wiseowl, I am getting error code 1004 . At this point :
Set qt = ws.QueryTables.Add(Connection:="URL ; " & URL, Destination:=Range("A1"))
what could be the problem? I have tried reading around but can't seem to get a solution!
Hi Jeff, it looks like you've put a space between URL and the ; character. Remove the space and you should have more success. I hope that helps!
@@WiseOwlTutorials Thanks alot, it now works fine
greate sir
Hi teacher
i have a question :
i think if i want to request only one table from webpage it is sufficiant to write only (.webtables = "table number") without the need for combining it with "websellectiontype" , i tryed that and it works wothout problems , is there any comments for this point or am i right !!
Hi Mahdjoub, according to Microsoft's documentation, you should use the WebTables property "only when the query table's QueryType property is set to xlWebQuery, the query returns an HTML document, and the value of the WebSelectionType property is xlSpecifiedTables" docs.microsoft.com/en-us/office/vba/api/excel.querytable.webtables
However, this wouldn't be the first time there has been an inaccuracy in Microsoft's documentation and it seems that you have discovered another one! My feeling is that you should always aim to be as specific as possible when writing code.
@@WiseOwlTutorials well understood
Many thanks teacher 😊
I'm trying this VBA to get a data in Amazon Store. I just wanted to get all the prices, names and ratings in each product in a specific category, but it didn't work :(
i think they are not a table form.... you could try to use web scrapying to get all the data in Python
for me it always hangs
Can you give me the general steps to automating login into a Website then downloading pdf reports and parsing out the pertinent information to an excel spreadsheet. I work in publishing and one of my daily tasks is to manual take the information from this pdf and enter it on a spreadsheet to track the responses to promotions.