Excel VBA Pull Data From A Website

Share
Embed
  • Published on Dec 11, 2010
  • The website has changed since I originally made this video. The state appears to have been removed but you can still get the city and county at least. Change the code from what I say in the video to this:
    Dim sDD as string
    sDD = Trim(Doc.getElementsByTagName("dd")(0).innerText)
    sDD = split(sDD,VbNewLine)(0)
    Range("city").Value = Split(sDD,", ")(0)
    Range("county").Value = Split(sDD,", ")(1)
    A tutorial showing how to pull data from a website. In this tutorial I make a zip lookup that pulls in the city, state and county based on a given zip code. Since posterous closed: brettdotnet.wordpress.com/2012/04/20/excel-vba-pull-data-from-a-website-update/

Comments • 250

  • Jayjay F
    Jayjay F 8 days ago

    Aren’t u supposed to use square brackets for indexing ?

  • Nikhil Gaikawad
    Nikhil Gaikawad 10 days ago

    hi I need to link google sheet to excel sheet plz kindly help to save my job

  • Hoàng Giang
    Hoàng Giang 26 days ago

    Follow my channel. Thanks all ru-clip.net/p/PLizX1pU6n281wvhHJMLjlmjNAs0Ox0G2Z

  • S SS
    S SS 3 months ago

    Excellent code. So cool.Thanks.

  • 吳百正
    吳百正 5 months ago +1

    I would use:
    If Target.address = Range("zipCode").address Then
    ...
    End If

  • Diego Lozano
    Diego Lozano 5 months ago

    awsome video, thank you sooo much.

  • Sakthivel Vajjiravel
    Sakthivel Vajjiravel 5 months ago

    like from tamilian tamilnadu india

  • Sakthivel Vajjiravel
    Sakthivel Vajjiravel 5 months ago

    you are god of excel

  • Riaz Rasool
    Riaz Rasool 7 months ago

    Hello! Can you help me on this? This is regarding stock trading. If a cell value is 'sell or buy' then it should automatically record the stock's (live current price). And after a while when 'sell or buy' disappears (cell value is empty) it records the changed current price into another cell beside previous record. I am already taking live feeds from google finance. For example suppose the price keeps changing over a period of time, lets say (cell A1) value is Sell/Buy and the price (Cell B1) is 235, so it records 235 and put it in cell C1. After some time if A1 is empty and B1 value is 200, then it should paste 200 in D1. That means we will have two price C1=235 and D1=200. It should be automated and NO BUTTONS TO CLICK via macro. Is it possible?
    Thanks!

  • ABHISHEK PATEL
    ABHISHEK PATEL 7 months ago

    I'm trying to click. A button on a web site but am unable to do so
    The HTML code is

  • Chinuch Files
    Chinuch Files 7 months ago

    you are awesome! never saw such a crystalized tutorial you are absolutelt amazing!

  • Gaurav Khiwasara
    Gaurav Khiwasara 7 months ago

    Thanks for this video.
    Its very helpful

  • remya raju
    remya raju 8 months ago

    Is it possible to create a macro code to retrieve content from website and compare it with database data?

  • A D
    A D 8 months ago

    Very nice information, I really wants this information and I get it, could you please share your email id so I can atleast ask questions if I unable to success.

  • Aleksandrs Bebrišs
    Aleksandrs Bebrišs 9 months ago

    What the knowledge...... man You are VBA God. Thanks!

  • Sarah Ershova
    Sarah Ershova 9 months ago

    I recommend new version worked version if not working disable a antivirus program.My working software on this link yadi.sk/d/kV_wQqU83KFZ3A

  • Lance Davis
    Lance Davis 10 months ago

    Very impressive!

  • Jose Manuel Diaz
    Jose Manuel Diaz 10 months ago

    How can I retrieve something inside the tag? See below, the tag is h3


    Senior Reservoir Engineer


    and I need to retrieve (capture) the value corresponding to "a href=", which is: "/jobs/senior-reservoir-engineer-oslo-norway-8?cmp=js&from=job-search-form-1"

  • Chris Breeling
    Chris Breeling 11 months ago

    What if you want to look up a entire column of zip codes and have the resulting data appear in the column next to the zip code?

    • Bugg Smasher
      Bugg Smasher 6 months ago

      Just exploring possibilities with code myself Chris, but maybe you could use a loop?

  • SURESHKUMAR S
    SURESHKUMAR S 11 months ago

    Thanks for this very helpful video.

  • Chiky Scares You
    Chiky Scares You 11 months ago

    holy shit, that's complicated af

  • Joemar Elibeto
    Joemar Elibeto 11 months ago

    Hi.... im getting an error in line
    If Target.Row = Range("zip code").Row And _
    Target.Column = Range("zip code").Column Then
    i dont know what to do... pls help me... :(

  • MyBI Business
    MyBI Business 11 months ago

    Well done DonFretBrett, this is a great explanation of the power of IE & HTML objects in VBA. Thank you very much.

  • Jagat Randhawa
    Jagat Randhawa Year ago

    My webpage requires to click a button instead of pressing enter....What do i do?

  • Wai Tsang
    Wai Tsang Year ago

    Hi. Is this for US only or can you find Zip codes from any country?

  • Manoel Marques Ribeiro

    Bom dia, parabéns pelo trabalho!
    Poderia me ajudar com a dúvida no link abaixo?

    www.clubedohardware.com.br/forums/topic/1308679-importar-p%C3%A1gina-da-web-para-excel-com-%C3%ADcones/?tab=comments#comment-7156645

    Desde já agradeço!

  • Eduardo Toscano
    Eduardo Toscano Year ago +2

    Hi Can I do this with USPS tracking system?

  • Unknown Source
    Unknown Source Year ago

    Hi sir can you help me for error ive encounter

  • Ganesh Shahane
    Ganesh Shahane Year ago

    Could you help me with your email ID so that I can email you my problem for which I need your help.

  • Deepak Kumar
    Deepak Kumar Year ago

    Can we make hole processes reverse
    excel to web auto fill auto click

  • Pravish Shetty
    Pravish Shetty Year ago

    Hii just a doubt.. Which I want to clear somewhere I have read that if we do it we are violating Google terms and conditions is it so.

  • Ammad Ur Rehman
    Ammad Ur Rehman Year ago

    Thnks.......

  • Jia Chen Yamashita

    thank you for sharing

  • Ernestas Raudys
    Ernestas Raudys Year ago

    Hello. Maybe can help make with other website similar system. I make witg google sheet but work slow?

  • Jonas Sami
    Jonas Sami Year ago

    if not intersect(target, range("zipCode") is nothing then

  • Danny Tan
    Danny Tan Year ago

    Hi, Brett
    I need a similar Code to extract data from website shipserv.com/impaweb/?name=
    Possible for your assistance? Please advise if token fee require
    Danny

  • clyburn209
    clyburn209 Year ago

    What if zipCode, County, and State are column headers, and each row has a different zipCode, can I use this VBA to load up the data for each row?

  • Alan Lehmann
    Alan Lehmann Year ago

    What if you had two text boxes, how can you combine the two in the URL....for example: ?place=95818 works for the ZIP, what if there was a box for State and ?state= California also worked. How could I put the two together into one single URL? I have a webpage like that and I've tried everything & & %26.....or is each webpage different?
    Love this video!

  • Tussengassed
    Tussengassed Year ago

    This is probably the clearest tutorial within 10 minutes i've seen in a loooong time!

  • Pierre-Emmanuel giuglaris

    Hi all,
    I don't know for you but when I type the first line, there is a error message which say : "expected: Then or GoTo" and the rest of the code bug.
    Is there anyone having the same problem as me ?
    Thansk in advance for your reply

  • Dheeraj Mehar
    Dheeraj Mehar Year ago

    stackoverflow.com/q/47240964/8925068
    Ur guidance needed

  • mithun nair
    mithun nair Year ago

    Thank you for ur video but please tell me How i can accommodate to over ride the certificate error message and click the link "Continue to this website (not recommended)."
    Html tag
      Continue to this website (not recommended).
    Kindly let me know if we can click the link and proceed to the website.
    Thanks in advance.

  • sh a
    sh a Year ago

    the web I am reffering has tables and doesn't show what I need to add after ? in URL.

  • Michael B
    Michael B Year ago

    You're a genius. Great coding. :)

  • lo kelly
    lo kelly Year ago

    I hope you may able to help as you are the Excel Guru!!!
    Now, my situation is I have the excel list, which is contains the company name, I want to create the query from web search to wrap the company contact.
    The company name will be excel sheet column "A" and paste the contact data in column "B".

  • Dom Powell
    Dom Powell Year ago

    This was very helpful, thank you!

  • Alex Clark
    Alex Clark Year ago

    I dont know if you are still replying to comments on this video but it is one of the most helpful and well explained tutorials i have ever watched on any subject. Thank you! I now am using it on much more complex websites and have run into an issue where my desired information is not under its buried in then then

  • remy Khuong
    remy Khuong Year ago

    Omg !!!

  • snowiethetoolguy
    snowiethetoolguy Year ago

    Nice Vid. Thanks

  • Peter Saldana
    Peter Saldana 2 years ago

    How would you do this if there were a list of zip codes that you do not want to enter 1 by 1?
    Thank you

    • Miguel Hernandez
      Miguel Hernandez Year ago

      I'd imagine this can be done using a recordset

    • sh a
      sh a Year ago

      I like to know if this posible.

  • Bryan Senker
    Bryan Senker 2 years ago

    Can you do one for Yellow Pages?!?!?!

  • Rep Life
    Rep Life 2 years ago

    This DD stuff is annoying. You've said dd so many times, but this video helped me regardless kinda. Except you didn't talk about how to populate the fields in the page, just took the easy way out since they place nice with their url,

  • Excel Curiosity
    Excel Curiosity 2 years ago

    its not working

  • Rodrigo Maroja
    Rodrigo Maroja 2 years ago

    Very helpful, solved my problem!!!!!

  • Amit Bodhwani
    Amit Bodhwani 2 years ago

    Hi,
    There is a text box on my webpage which on clicking will pop up a calendar the properties of that text box is

    I tried clicking that button using IE.document.getelementbyid("datepicker") and IE.document.getelementsbyclassname("hasDatepicker") and also IE.document.getelementsbyname("date") but i failed everytime and i am not able to click text box because of which the calendar on my webpage is not appearing.
    I tried using Selenium Java code and it is working fine with it but i want to code with VBA scripting.
    Please help me with this issue as while running i am not getting any errors also.
    Thank You

  • JAY C
    JAY C 2 years ago

    bravo! good sir

  • Peter Saldana
    Peter Saldana 2 years ago

    Thank you so much for this. I am actually working on this but for schools. Meaning I would like to put in the name of a university or college and get their city and state in return. Could you help me please?

  • hubster69jdj
    hubster69jdj 2 years ago

    too outdated for beginner educational purposes

  • dondon1
    dondon1 2 years ago

    Thank you DontFretBrett! This is very helpful. :)

  • Daniel Araujo
    Daniel Araujo 2 years ago

    I learned a lot from your tutorial, thanks!

  • 24October 2016
    24October 2016 2 years ago +7

    The site changed too much man, make another video :(

  • Li Lin
    Li Lin 2 years ago

    Hi, thanks for the great video tutorial. I am trying out a similar website based on your tutorial.
    I have a question: what if the website that I am extracting the information from requires an account to login? How do I use VBA to log into an account first then extract information.
    Would appreciate if can provide some advise to this:))))

  • Pramod Yadav
    Pramod Yadav 2 years ago

    Cool :) !

  • Sathish Ram
    Sathish Ram 2 years ago

    hi
    I have one doubt hope you can solve it.

  • ringh93
    ringh93 2 years ago

    @DontFretBrett
    How do I code If I want to pull data from a website containting:
    Url, Name, email. phonenumber From say 3000 companies on that website but it should all be automated.
    So the program should go through each page and only choose companies having those things?

  • Scott Kirkwood
    Scott Kirkwood 2 years ago +9

    Holy cow...that is literally THE most beneficial thing I've learned with regard to VBA. Been working with it for 10 years now, and I just recently came across a need to interact with an HTML page. So, back to Google, which led me here...and now, the world is again, full of endless possibilities!!!

    • David Durrenberg
      David Durrenberg 5 months ago

      @Kevin moehring My comment was 2 years ago and nothing to do with phonetics. The statement was in response to the flawed logic that the World would "ONCE AGAIN be full of endless possibilities". If the World was ever before full of endless possibilities, the world would just CONTINUE to be full of endless possibilities, not ONCE AGAIN full of endless possibilities, because those "endless possibilities" would never have ended in the first place. Also none of my sentences were fragments. Read a book or two and see if you can raise up your intelligence level. Do you even know what phonetics are?

    • Kevin moehring
      Kevin moehring 5 months ago

      @David Durrenberg Your second sentence wasn't a sentence. It was a fragment. There shouldn't be a period. If you're going to troll someone's use of language, try getting it right yourself. Plus this video was about code; nobody watching this video cares about the finer points of the phonetics found within the English language.

    • Steven m
      Steven m 2 years ago +5

      you are an idiot, david

    • David Durrenberg
      David Durrenberg 2 years ago

      Attempting to educate you on the English language.

    • Scott Kirkwood
      Scott Kirkwood 2 years ago +8

      Why all the negativity? Was there a purpose to that?

  • Nabila Naorin
    Nabila Naorin 2 years ago

    it's amazing that it can be done using vba

  • Prajay Pawar
    Prajay Pawar 2 years ago

    How can I paste the data in two many column

  • hamzaa413
    hamzaa413 2 years ago

    hi mate could you possibly help me do a vba for extracting data from the UK DVLA when the reg and make is entered then the car details are assigned.

  • Pgordons
    Pgordons 3 years ago +2

    Could you do the same thing but this time extracting a specific stock quote from yahoo finance's website? I can't really understand which part of the html code I need for the macro...

  • Tanawat Oonwattana
    Tanawat Oonwattana 3 years ago

    YOU ARE GENIUS

  • Parthiban Shanmugaraj
    Parthiban Shanmugaraj 3 years ago

    @DontFretBrett thanks for the wonderful video. can you please help me how to loop this macro. Suppose i want to get only the country name for many zipcode, at this situation how can i loop ?

  • Hellmut Matheus
    Hellmut Matheus 3 years ago

    dude you rock

  • Yuva stanza
    Yuva stanza 3 years ago

    Funtastic and searching for this crawler video :D. Thanks +DontFretBrett

  • TT Smasher
    TT Smasher 3 years ago

    I'm trying to apply this example to get the price on Amazon but it's not working, could you please make a tutorial for it please ?

  • HIMEL SARKAR
    HIMEL SARKAR 3 years ago

    ?

  • HIMEL SARKAR
    HIMEL SARKAR 3 years ago

    How I get mail ID ,phone number ,country, zipcode ,city,districk, from Google ,

    • Vitto
      Vitto 2 years ago +1

      you haxxor le peeps

  • HIMEL SARKAR
    HIMEL SARKAR 3 years ago

    100thanks

  • TheAybab
    TheAybab 3 years ago

    I keep getting "Compile Error: Sub or Function not defined" with my version. What could be causing this? Thanks!

  • Ardy Sherafati
    Ardy Sherafati 3 years ago

    Thanks for the great video. It was very helpful. I have a request. I am trying to extract the products data on amazon. I am able to get all the information other than the description. I can't figure it out. Please check the following example page:
    www.amazon.com/Tetley-Premium-Loose-31-75-Ounce/dp/B001VIIXXQ%3FSubscriptionId%3DAKIAJL6OJYQM2ZLPMWEA%26tag%3Dbarcodelookup-us-20%26linkCode%3Dxm2%26camp%3D2025%26creative%3D165953%26creativeASIN%3DB001VIIXXQ
    Please advise.

  • Manoj Prabhakar
    Manoj Prabhakar 3 years ago

    Hello, your video was so good. Thank you. I'm doing a similar project and could you please help me how to import the date from the following source code instead of the text as shown in your video.

  • Jean Chartier
    Jean Chartier 3 years ago

    I have a problem while using the dim function
    it refuse to create the variable

  • yuyu yu
    yuyu yu 3 years ago

    1

  • Mel Slabaugh
    Mel Slabaugh 3 years ago +3

    Can I hire you to do a simple Excel job? I say simple because you make it look so simple!

  • Mike Cantu
    Mike Cantu 3 years ago +2

    How you doing. I'm trying to pull table data from here rotogrinders.com/team-stats/nfl-allowed?sport=nfl&position=1&site=fanduel&range=season and insert it into excel. What would be the vba code to do that I've looked all over and a regular vba web query doesn't work. Thanks

  • Reynel Canseco
    Reynel Canseco 3 years ago

    hi i have a question can i configure to do the same but instead to look up by zipcode can i look up by city name?

  • Sandeep Shrestha
    Sandeep Shrestha 3 years ago

    The code works perfectly, thanks.
    I was wondering, how can the code be applied to the columns right to the B column? I mean how can the coding be changed so that zip code entered in C1, D1 or so forth would give the results in their respective columns?

  • Ry PI
    Ry PI 3 years ago

    Hi Brett, How R U My Friend? Hope U R Well. Here is a good one for you: Royal Mail API. Do you thing you could integrate this into Excel so I can send my orders direct to Royal Mail to print the shipping Label? Website with all the info can be found here: www.royalmail.com/corporate/services/shipping-api.

  • ashley
    ashley 3 years ago

    This is a great video! You taught me a bunch of stuff I didn't even know to ask for.

  • jmayache
    jmayache 4 years ago

    Great tutorial, thank you for your contribution. I am trying to pull data from Yahoo finance based on Rows values (i.e Symbol Values). I was able to get what I need based on your Tutorial for one row (i.e one Symbol). How can I accomplish the same for multiple rows (i.e different symbols)? Resulting values are displayed in the column adjacent to the Symbol Column

  • askjerry
    askjerry 4 years ago +1

    I am attempting to pull data from an API... if you type in this string for example...
    api.openweathermap.org/data/2.5/weather?q=austin,us
    It will return a text string containing all sorts of data. Once I have that string... I can parse it and pull whatever I want... getting that string... I'm close... but not quite.
    Excel sheet with two cells (CITY) and (OUTPUT) which also has a button, clicking the button calls the routine.
    In theory, I pull the returned data to a cell or a string... then I can pull what I want from it.
    Option Explicit
    Sub PULL_data()
    Dim IE As New InternetExplorer
    IE.Visible = True
    ' Drop in the CITY (assume US for testing)
    IE.Navigate "api.openweathermap.org/data/2.5/weather?q=" & Range("CITY").Value & ",us"
    Do
    DoEvents
    Loop Until IE.ReadyState = READYSTATE_COMPLETE
    DIM mydata as string
    ---> SOMETHING HERE

  • Brian Powell
    Brian Powell 4 years ago

    Absolutely wonderful video. Thanks man!

  • Katink
    Katink 4 years ago

    too difficult

  • Tarık Can Erbaş
    Tarık Can Erbaş 4 years ago

    Run-time error '91':
    Object variable or With block variable not set

    • Jayfox
      Jayfox 4 years ago +1

      @Tarık Can Erbaş "sdd = Doc.getElementsByTagName("dd")(1).innerText" wil not work anymore as there's now only 1 so you have to change it into "sdd = Doc.getElementsByTagName("dd")(0).innerText"

  • Andrei Sweet
    Andrei Sweet 4 years ago

    You sir are brilliant!

  • Sinan Çetinkaya
    Sinan Çetinkaya 4 years ago

    "If Target.Address = Range("zipCode").Address Then " would be better ;)

  • zilchsr
    zilchsr 4 years ago

    Great video. A great augmentation would be to have included an error handling subroutine for invalid Zip Codes, for which Excel will throw a runtime error. Here would be my recommendation:
    On Error GoTo ErrorHandling ' add this line before assigning a value to variable sDD
    sDD = Trim(Doc.getElementsByClassName("std-address")(0).innerText)
    On Error GoTo 0 ' add this line after sDD has a value
    '.
    '. (the rest of the code)
    '.
    End If
    Exit Sub ' add this to prevent unwanted execution of subsequent subroutine
    ' add the following lines to the very end of the code
    ErrorHandling:
    MsgBox "Zip Code is INVALID", , "Bad Zip Code"
    Exit Sub
    End Sub

  • Dilip
    Dilip 4 years ago

    Great knowledge & your help is really appreciable!! I like your speed of typing the code also

  • Future Atlanteans
    Future Atlanteans 4 years ago

    Very weird but in excel 2013 your code gives all sorts of compile errors. Can anyone please help me out ?

    • T Larson
      T Larson 4 years ago

      @Blobbejaan Blob Try "And _" (note added space)

    • Future Atlanteans
      Future Atlanteans 4 years ago

      @Thad Larson Yes but I already get an error after I press enter after this line;
      If Target.Row=Range("zipCode").Row And_
      I get compile error saying "expected: then or goto"

    • T Larson
      T Larson 4 years ago

      Did you use the code in his (Show More) comments? Except that ("dd&q­uot;) should be ("dd").

  • Future Atlanteans
    Future Atlanteans 4 years ago

    Thanks for this very helpfull video.