Import Web Data to Excel using VBA Macros

Поділитися
Вставка
  • Опубліковано 24 лис 2024

КОМЕНТАРІ • 157

  • @TeachExcel
    @TeachExcel  2 роки тому

    MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_IOzHacoP-u4
    200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content

  • @veggiemix1994
    @veggiemix1994 4 роки тому +3

    I wasn't looking for getting stock price but the title of this video caught my eye and I watched the whole video. I like the speed of your talk: slow and clear. Students take time to learn new information and the delivery of your talk is just the right pace! Thank you!

    • @TeachExcel
      @TeachExcel  4 роки тому

      You are very welcome! And thank you very much for your comment! I'm glad you like my pace! :))

  • @RenLord46
    @RenLord46 Рік тому +2

    This tutorial is pure gold, I tried to do something similar with the help of AI, but this video is what helped me the best, thank you.

  • @92lucasborges
    @92lucasborges 4 роки тому +8

    I dont comment too often, but I had to for this one. Brilliant! Thank you for sharing

  • @TheAJKahn
    @TheAJKahn 3 роки тому +3

    The most simplest macro I've found with the simplest explanation. Great video. I wish I could've found your video earlier. 5 Thumbs UP!

    • @TeachExcel
      @TeachExcel  3 роки тому

      I am so glad you think so! Thank you so much for the nice comment :)))

  • @evehenare4162
    @evehenare4162 5 років тому +6

    Great tutorial thank you, tied together alot of concepts for me. I like how youve given extra explanation like synchronous execution or the grouping for an IF statement, but given basic overviews and said what to do to make it work. Thank you!

  • @aliilhanhaliloglu5974
    @aliilhanhaliloglu5974 2 роки тому

    MOMMM I have just found the best video on the internet

  • @calvinjames419
    @calvinjames419 2 місяці тому

    Love your simple code and explanation

  • @brandonspiegel2293
    @brandonspiegel2293 5 років тому +5

    Thanks for the knowledge man! I will use the knowledge to get closer to my first million man!

  • @NguyenAnh-xn4pb
    @NguyenAnh-xn4pb 4 роки тому +7

    Hi there, I got run-time error'91' object variable or with block variable not set at command price. Could you help me figure out what it is?

  • @SunilMathewMusic
    @SunilMathewMusic 3 роки тому +1

    This is one the best tutorials I have even seen 👌💥Top Class

  • @dino-art
    @dino-art Рік тому +1

    Excelent way with which i introduced objects to my VBA learning :D

  • @juergon69
    @juergon69 Рік тому

    Thank you for this tutorial. I arrived to this tutorial searching a way how to solve a "the inner text retrieved values does not refresh...". It seems from the video that you encountered the same problem even though you are using the since 2000 year...Best regards

  • @gregNFL
    @gregNFL Рік тому

    Very well explained. Thank you.

  • @Mrshanmugham1
    @Mrshanmugham1 Рік тому

    Excellent Excellent, What I want, working fine, Thank you very much Brother

  • @juergon69
    @juergon69 Рік тому

    Great explanation

  • @michaelkaczmarek8576
    @michaelkaczmarek8576 5 років тому

    Great video! I am new to VBA Macros and trying to figure out how to add to a list of product EAN numbers their price according to Google Shopping. This seems to be a great base to make such a macro. Now I only need to:
    a) Make the macro adapt the url for each EAN code entered into Google Shopping search field
    b) Insert the price into the corresponding cel instead of opening message boxes

  • @ismaeelahmad3752
    @ismaeelahmad3752 Місяць тому

    Very nice video and very nice way of teaching. I have question, I want to extract data from Electricity bills site on the bases of Reference numbers. Please give some guidance in this regard.

  • @KnuYorker
    @KnuYorker 5 років тому

    This clarified many doubts that I had. Thank you.
    I look forward to using this technique soon.

    • @TeachExcel
      @TeachExcel  5 років тому

      Glad to help! Let me know if you have any questions!

  • @carterbates1026
    @carterbates1026 5 років тому

    Super helpful video, especially for someone like me with little VBA and html experience. I was looking for a method to pull up-to-date stock price information so this was perfect,.

  • @inactiverahul
    @inactiverahul 3 роки тому

    thank you very much for the video

  • @hadeelo
    @hadeelo Рік тому

    amazing video and working 100% the best I found soo far but if my website have username and password what changes I need to do for the same code explained in the video

    • @hadeelo
      @hadeelo Рік тому

      Is it possible to make excel login first then get the data from other page after login using the method in the above video the the get element by class name

    • @hadeelo
      @hadeelo Рік тому

      reminder

  • @Leo93554
    @Leo93554 5 років тому

    Very nice tutorial, well delivered! keep it up!

  • @muhammadfaizankhan6066
    @muhammadfaizankhan6066 4 роки тому

    Thanks! Very helpful video

  • @The-Vay-AADS
    @The-Vay-AADS 3 роки тому

    man I would LOVE to hear more about the request header! specifically - a crypto exchange API requires the header to be sha256 encrypted (with some user data as keys) and i am stumbling around the internet, trying to learn how to do it. :D :D
    so yeah, header love!

  • @ghitaqabli7497
    @ghitaqabli7497 2 роки тому

    Thank u for this video! I have question, how can I get data updated?

  • @lloydmcleod629
    @lloydmcleod629 3 роки тому

    Very good.

  • @user-eb3rn8su8e
    @user-eb3rn8su8e 2 роки тому

    can i use this whit many web pages? Can i use user input take them or excel coloumn

  • @russwilde9350
    @russwilde9350 2 роки тому

    This macro is amazing! Question if I wanted the data to populate a cell how can I do that?
    Thanks!!!

  • @chrishollis5342
    @chrishollis5342 4 роки тому +2

    Will this work with a webpage that has user name and password?

  • @alndr4u
    @alndr4u 3 роки тому +1

    In Excel, sheet1 range B1:B200 contains many website links of zip files, download the zip file of all those links together and keep them in the same folder using vba, make a video for that.... Thanks

  • @bijumohan2405
    @bijumohan2405 3 роки тому +1

    Hi, it was an excellent piece of knowledge shared in a very simple way, thanks a lot
    Is there a way to get the value from website to a cell in excel?

  • @knives9758
    @knives9758 4 роки тому +2

    Hi, very new to excel
    Great vid but I have a few questions
    1) Can and how do you pull multiple items from a website.
    2) How do you add in a step to access a website that requires you to log in first?
    3) How do you automate this to pull the same date for a series of stock symbols?
    Thanks,
    R-

    • @edwinmiranda4005
      @edwinmiranda4005 4 роки тому +1

      You have to use getelementbyid on the username input field and password input field to log in to website. Right click on the username/password input boxes then click inspect element. There’s many sources on stack overflow or some google searches can get you some help.

    • @edwinmiranda4005
      @edwinmiranda4005 4 роки тому +1

      I do recommend looking up wiseowl on UA-cam. He’s got very informative videos on web scraping and VBA/internet data pulling

  • @KLiCuk1
    @KLiCuk1 3 роки тому +1

    Great vid thank you. is there a way to pull the data into a cell such that the cell is always pointing to the element on the page and will update as soon as the price updates?

    • @MKitson
      @MKitson 3 роки тому

      Would like to know this also - anyone?

  • @chrisgod397
    @chrisgod397 3 роки тому

    hey just wondering if I can get the output data to be shown into a cell

  • @karthiksn7851
    @karthiksn7851 3 роки тому

    Can u plz make a video on how to fetch cookies from a particular website and paste those cookies into an excel file

  • @burinoderoma
    @burinoderoma 4 роки тому +2

    Hi @TeachExcel. First of all great job. Loved the way you presented it, super useful. One question for you or anyone who's going to read this. Could you please explain how to loop this macro? Say for instance, I have a list of internet websites in column A and want to return the output in column B...so website in A1 return the result in B1, website A2 return the result in B2 and so on. Anyone who can help me with this? thanks a lot in advance.

  • @cheong6b
    @cheong6b 4 роки тому

    Thank you. It is amazing

  • @johnharoldverzosa6138
    @johnharoldverzosa6138 4 роки тому +1

    i tried editing the code the website and the element name. but i got a run time error 91. can help me?

  • @EagelzOfficial
    @EagelzOfficial 5 років тому +3

    Thanks for the video!
    When I change the website and the Class, I get the Run-Time Error '91': Object Variable or With Block Variable Not Set. How can I fix this?

    • @TeachExcel
      @TeachExcel  5 років тому

      It's probably due to the html tags not being present on the new page. If you post this question on our forum, you can upload a sample Excel file or the code you're using, and it will be much easier to help: www.teachexcel.com/talk/microsoft-office?src=yt

    • @andrewdimartino2663
      @andrewdimartino2663 5 років тому

      @@TeachExcel could this be caused by using a VPN?

  • @inspirz
    @inspirz 3 роки тому

    Hi. Great tutorial! Instead of opening it as a MsgBox how do I copy it into a spreadsheet?

  • @abdessamedghettas7167
    @abdessamedghettas7167 2 роки тому

    Thanks

  • @rvas1966
    @rvas1966 4 роки тому

    Great job... What do I do to get top 3 closing price in end of the day reports automatically

  • @ishaq79
    @ishaq79 4 роки тому

    Hello there, i wanted to know if i can do the same for the internal bar code website, from there i can get my daily production, stock and dispatch data??

  • @cliveharvey9991
    @cliveharvey9991 4 роки тому +2

    how do you then take the value that has been extracted from the website to excel spreadsheet?

    • @casiora7217
      @casiora7217 4 роки тому

      and I would like to know this because I am a beginner

    • @bettadpurshridhar3801
      @bettadpurshridhar3801 4 роки тому

      @@casiora7217 if it is digits only without spaces/other characters that has been imported use the VALUE() function. If import has spaces and or other characters, you could use a combination of LEFT(), RIGHT() functions and then apply VALUE().

    • @abhijeetjoshi5451
      @abhijeetjoshi5451 3 роки тому

      you can replace "msgbox price" with range("A1").value=price to get the price in cell A1

  • @matrix26uk
    @matrix26uk 3 роки тому

    How would I adapt this to export data from a website and create a table in a worksheet?

  • @ca.arifhasan7996
    @ca.arifhasan7996 3 роки тому

    Thank you so much sir
    Can it work in MS Office 2010?
    I can pay for this sir please reply

  • @eduardocapaneli1141
    @eduardocapaneli1141 3 роки тому

    Nice Man! ;)

  • @alternativeenergyresearch4857
    @alternativeenergyresearch4857 4 роки тому

    Also how to prepare Excel RTD to send data to charting software prepare a VBA code so that Excel sheet will poke out data in software

  • @carlfitch3835
    @carlfitch3835 2 роки тому

    Hello thank you for the helpful video! Whenever I try to run the macro it gives me the error "use defined type not defined" and point to the "Dim html As New HTMLDocument" as the error. Any suggestions would be appreciated, thanks!

  • @jwuj97f
    @jwuj97f 3 роки тому

    Thanks, it is possible to modify this code to pull API data from my Website DB? I am looking for small (compact code) to retrieve data from my Website, into my Excel objects. Great video

  • @GoodVibes-qg4ko
    @GoodVibes-qg4ko 3 роки тому

    Is there a video that takes your code and can modify it to pull in elements by tag name? I think sites like tipranks or nasdac use Javascript

  • @slambergamer91
    @slambergamer91 4 роки тому

    how do we use xml library to enter in search box and click button?

  • @Dhanu_369
    @Dhanu_369 Місяць тому

    Can you explain vba runtime error 462 and how to solve it

  • @Surya-di7ed
    @Surya-di7ed 4 роки тому +2

    and how to fill the data/value that we got in to the cell ?

    • @jonbylo
      @jonbylo 4 роки тому

      I would like to know how to do this too.

    • @abhijeetjoshi5451
      @abhijeetjoshi5451 3 роки тому

      you can replace "msgbox price" with range("A1").value=price to get the price in cell A1

  • @jeffkasavan93
    @jeffkasavan93 3 роки тому

    It looks like an internal website I want to use has ID, not Elements - how do I modify the macro?

  • @dosmastrify
    @dosmastrify Рік тому

    what if you had to use drop downs to get to the html page you needed

  • @jeffreygonzalez7653
    @jeffreygonzalez7653 3 роки тому

    Hello, can we get 2 websites on one module? Not sure how to call two websites in one vba

  • @varunrain8763
    @varunrain8763 3 роки тому

    Mate, could you please do a tutorial on how to get the high and low values of the stock from yahoo finance? I can't seem to make it work with the same method

  • @yogendramehta3245
    @yogendramehta3245 4 роки тому

    Will this work to import all Utube video url data from a monthly filtered history to an EXCEL file ??
    Appreciate yr confirmation
    Thx

  • @barisyakut7970
    @barisyakut7970 5 років тому

    Literally, the best informative video on UA-cam. I don't even know how to write a simple macro but I managed to get what I wanted. Great stuff.
    Just a question: Suppose I need to get data from multiple websites. Do I need to create separate request, response, html, website, price variables for each website or can I just reuse those variables and set their values for each different website?
    For example, request variable seems not to be a website specific. After I retrieve data from the first website, I can simply reset the website variable for the next website that I'd like to get data from and just do a request.send without doing Set request, request.Open and request.setRequestHeader again. Would that be a problem?

  • @siriobellazzecca4152
    @siriobellazzecca4152 Рік тому

    How do i inport phone numbers to a realstate exel sheet with company name and license number

  • @AWonderfulLife5689
    @AWonderfulLife5689 4 роки тому +1

    Thanks for the tip. I have a question though. What if instead the value will be displayed in the message box, the value should be written in the excel file for example cell A1? :-)

    • @TeachExcel
      @TeachExcel  4 роки тому +4

      make a range reference and put the value in there, like Range("A1").Value = theVariableWithValue where the theVariableWithValue is a variable that holds the value you got and want to store.

    • @AWonderfulLife5689
      @AWonderfulLife5689 4 роки тому

      @@TeachExcel thank you so much !!

  • @alvaroagnrmcf
    @alvaroagnrmcf 3 роки тому

    How could it be to import a table from an inner company website where you have to chose two times and two days? For example, data from 15:00 to 00:00 on 14.02.2021... what could it be the change?

  • @kesiah2870
    @kesiah2870 4 роки тому

    I did follow this, but I stopped at getElementsByClass. The instruction says to list the products + variation to excel/ Balloon and party website has a lot, and I don't know where I can start for the elements.

  • @mikelennon1078
    @mikelennon1078 3 роки тому

    How can I download the template?

  • @mitalipatel2621
    @mitalipatel2621 3 роки тому

    Hii ! Great tutorial . Thanks for sharing.
    Just needed a quick help if you could share. I have a client who has given me a list containing different niches like florists, psychiatrists, doctors, clinics etc and different locations. He needs 3 rows for each niche in each location given. Data can be obtained either through Google maps or Yelp or some kind of website. So is it possible to create a macro for that ?

  • @matyaskatona93111
    @matyaskatona93111 Рік тому

    Thank you, it is a great tutorial.
    I can't get meta property content info.
    i need the value: 126990 but i can't get it. It is in the head tag.

  • @unknownuser-nd1lg
    @unknownuser-nd1lg 3 роки тому

    I did check the references that you are showing at 16:10 but VBA still does not know the datatype New HTMLDocument. Can you help me please?

  • @dryogayur
    @dryogayur 3 роки тому

    How save price in cell ?

  • @garymoister3313
    @garymoister3313 2 роки тому

    love this tutorial but i have a question, i changed the .item(0) to .item(1) and it gives run time error 91. works fine when there is only one instance in where the class name appears on the html body. but as keep getting this error when trying to get the 2nd or 3rd instance. please help me Mr teachexcel or anybody please

  • @rollybacanto1542
    @rollybacanto1542 5 років тому

    Hi just a question this is quite bugging me out, after we secure our macros in project properties is there a way to at least disable "run" button in ALT+F8? or can we hide from that macro window alt+f8 thingy our macros?

  • @vrino223
    @vrino223 5 років тому

    Is there a workaround for mac users? There is no official XML map available. Thank you

  • @gpniftytechnical4471
    @gpniftytechnical4471 5 років тому

    Thank you for the great information I also want to know the VBA coding to get my yahoo finance portfolio in excel. Is that possible.

  • @metinguler
    @metinguler 5 років тому

    Thanks a lot.

  • @omarhussein1118
    @omarhussein1118 4 роки тому

    Hey! Great code, but i have a small question. I want to extract same Span class from Multiple Website Links(they all similar and same span class). So what i did is I changed Website and put it "website = Range("E1") , and on bottom Output i changed msg box to "Range("A1").Value = Price. so it works on E1 and it puts what i need in A1 but i want it to automate on the entire Column. Like I wanna fill E Column with weblinks, and then Get it to fill Information from each web to the corresponding Column A, so price of link E1 goes in A1 / E2 goes in A2. Etc... And im very new to VBA so it has been quite rough to figure it out. Thanks!

  • @dr3wl3r
    @dr3wl3r 5 років тому

    Exceptional video! Much appreciated! My only question is: What if you are importing a table. For instance, the information you are trying to import is a few lines below the div class?

    • @edwinmiranda4005
      @edwinmiranda4005 4 роки тому

      Try For each loop on the tr or td tag on the table of the website. Look up wiseowl on UA-cam. He’s got videos on web scraping

  • @neerajpal4758
    @neerajpal4758 5 років тому

    Awesome 👍

  • @konipeis
    @konipeis 5 років тому

    This is fantastic news. However, I have one very crucial question. Is it possible to extract all the values from the graph below the 1.1322 value?

  • @rafighsajadi2247
    @rafighsajadi2247 4 роки тому

    what if the website needs to login for example like a Gmail?

  • @johng5295
    @johng5295 5 років тому

    You nailed it! You really have an inventive mind. This is learning by doing at its best . It is written that "A student is not better than the teacher, but the student who has been fully trained will be like the teacher"(Bible Luke 6:40). Thanks, you got the best stuff on earth. And, now I fill like I can code like my teacher--like you. "Great teachers are hard to find." How many more tricks you got? What's the best macro vba book for beginners that you might recommend me to buy?

  • @ZAKPARKOUR
    @ZAKPARKOUR 5 років тому

    Hi there, I am trying to use this approach to import a short string of text from a website into an excel cell. What modifications to this code would need to be made ?
    Thank you

    • @TeachExcel
      @TeachExcel  5 років тому

      I feel like I told you in the tutorial what to change - basically you need to tell the macro from which piece of the website to get the information - you can use an html tag ID to do this, usually.

  • @enrikekasijas705
    @enrikekasijas705 4 роки тому

    Thank you man! Maybe there is a way to learn VBA;P

  • @pragadeeshmurugesan9098
    @pragadeeshmurugesan9098 5 років тому +1

    Hi I got a error.msg run time error 438 objects doesn't support this property or method it indicate the line price = html.getelementsbyclassname(etc).item

    • @edwinmiranda4005
      @edwinmiranda4005 4 роки тому

      Try going on tools then references and the. checking the HTML controls box

  • @oprydor
    @oprydor 5 років тому +1

    Thanks a lot! It's really a valuable tutorial. Is there a way to get the same sort of data from password protected web sites? I know the password -:).

  • @abhijeetjoshi5451
    @abhijeetjoshi5451 3 роки тому

    The code is throwing me an error whenever the element goes above 9 (double digit)

  • @bijumohan2405
    @bijumohan2405 3 роки тому

    Currently the output is shown in a dialogue box or a form

  • @TheEvilOctorock
    @TheEvilOctorock 4 роки тому

    i love you

  • @madanraj8046
    @madanraj8046 5 років тому

    Is it possible to download data from website on every day basis ,like " want to download files from a website based on daily dated automatically ,the website consists different dates "

    • @gokulraj2929
      @gokulraj2929 4 роки тому

      Have you got the answer ?..... I'm also in search of it!

  • @garymoister1682
    @garymoister1682 2 роки тому

    Hi @teachexcel how would this be done for id, I've changed the macro so search for html.element by id but not working

  • @P-SC
    @P-SC 4 роки тому

    Where can I find the premium tutorial?

    • @TeachExcel
      @TeachExcel  4 роки тому

      I am currently in the process of making the premium macro course, which this will probably be a part of - you can signup for the newsletter on teachexcel.com if you want to be notified when it's released.)

  • @XAbuLujainX
    @XAbuLujainX 5 років тому

    can I use it for java script web site ? if yes, could you please till me how? thank you

    • @TeachExcel
      @TeachExcel  5 років тому

      That's a bit of a vague question but I have a feeling that dealing with js will be tough/very annoying.

  • @casiora7217
    @casiora7217 4 роки тому

    I also have a beginner's question: the result returned by this VBA, namely for example the figure returned after running the file of 1.1322 how can it be saved in a file to be visible and always updated when the file is opened? Thank you in advance.

    • @abhijeetjoshi5451
      @abhijeetjoshi5451 3 роки тому

      you can replace "msgbox price" with range("A1").value=price to get the price in cell A1

  • @zakariaakbar7566
    @zakariaakbar7566 2 роки тому

    not work in this my excel application, debug always on script "request.send" to bellow

  • @drs10301
    @drs10301 4 роки тому

    Dear Sir, what is the code for "waiting till webpage completely loads in chrome using excel vba" as it is "ie.readystate = readystate_complete" in internet explorer

    • @TeachExcel
      @TeachExcel  4 роки тому

      No idea. If you are going to use Office based code, like a macro from Excel, I'd stick with using IE though because there will be more support for it.

  • @davidmorales9706
    @davidmorales9706 4 роки тому

    How can I filter price within a class with multiple elements/attributes?
    33€99

  • @MySnoopy2000
    @MySnoopy2000 4 роки тому +1

    ERROR MESSAGE! This was working for over a year, but suddenly in the last month I've been getting the "Run-time error '91': Object variable or With block variable not set" error-message in regards to the "Price = html.getElementsByClassName("Trsdu(0.3s) Fw(b) Mb(-4px) D(ib)") .Item(0) .innerText". The error doesn't alway occur for the same share price I'm fetching on Yahoo Finance. I checked, and I didn't mistakenly change the code. Does anybody have a solution? Do I need to "Set"/add something to the already written "Dim price as Variant"? THANK YOU!

    • @MySnoopy2000
      @MySnoopy2000 4 роки тому

      In the "Locals Window" it's indicated that my Price expression has an "Empty" Value and a "Variant/Empty" Type if this helps. Thank you 1000!

    • @TeachExcel
      @TeachExcel  4 роки тому

      Probably the website changed a little - that is the problem with this procedure, we are at the mercy of someone else's website. If you post a sample file and description of this on our forum, it should be easy to help you out - www.teachexcel.com/talk/microsoft-office?src=yt_comment

    • @MySnoopy2000
      @MySnoopy2000 4 роки тому +1

      @@TeachExcel Thank you. I just added it with the title "Fetching Online Price (Yahoo Finance), VBA Error 91 "Object variable or with block variable not set".

  • @kathyroberts7777
    @kathyroberts7777 3 роки тому

    OMG. Do you also teach SQL for beginners?

    • @TeachExcel
      @TeachExcel  3 роки тому

      No I don't I'm sorry. SQL (and databases) is one of my secret pleasures in life, but I just don't have time to teach it. Right now, I'm all-in making the VBA course for TeachExcel

    • @kathyroberts7777
      @kathyroberts7777 3 роки тому

      @@TeachExcel Such a pity if anyone could teach me I am sure it would be you. You have an easy teaching style, and I love the way you precisely say some of your words.

  • @alternativeenergyresearch4857
    @alternativeenergyresearch4857 4 роки тому

    You have shown for one price how to pull data table stock price and volume table on web site I want to pull the table say 50 stocks the in table and two parameter current price and volume how to do it prepare a code

    • @abhijeetjoshi5451
      @abhijeetjoshi5451 3 роки тому

      you need to create a loop - do while cell blank loop

  • @dipakacharya8493
    @dipakacharya8493 4 роки тому

    How many API requests we can make. I mean for 250 employees - one get rest API which will update 250 students in cell is it possible above code in loops for 250 items?

    • @TeachExcel
      @TeachExcel  4 роки тому

      That depends on the web service you are calling and not excel.