Excel VBA Pull Data From A Website

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.wor...

[@Exceltrainingvideos]

One of my students gave me the link to your video. Excellent stuff. Thank you!

[@scottkirkwood3465]

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!!!

[@dp74durre]

"the world is again, full of endless possibilities". So the world was once full of endless possibilities, then at some point those endless possibilities ended, and now that you have seen this video, the possibilities are endless, until of course they end. You really should put some thought into the words you string together.

[@scottkirkwood3465]

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

[@dp74durre]

Attempting to educate you on the English language.

[@stevenm8970]

you are an idiot, david

[@kevinmoehring8259]

@@dp74durre 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.

[@guitardweep13]

You're the man! I've just started working as a data analyst, and my boss wants us to stay in excel and vba for prototyping, but his web scraper is messy and complicated as hell; your whole method is concise and well thought out. Props!

[@ja3sk3adik55]

I have moved from a few jobs for the last decade. I was here when you have just uploaded this video, but I always come back and refer back to it. Everyone thinks I'm a genius, thanks to you. Hehe. :P

[@osareafallire]

I spent all Friday afternoon trying to figure out how to do this and apparently forgot that I watched this video at some point. Soooooooooooo perfect!!

[@MarinosGo]

7:10 "...and that is how you pull information in an Excell Spreadsheet from a website" Thanx for watching THANK YOU MY FRIEND i am learning VBA the last month because i want to do Web Scraping and it feels like pulling freakin' teeth A Great Help indeed

[@kennethvela2784]

Your video is the best so far in scraping data into Excel. It's a perfect video with the perfect explanation. Maybe you can do a video where you scarp data from numerous websites in one spreadsheet

[@OriginalJoseyWales]

Man that is an AWESOME tutorial !!! This will be so useful for me at work. I work in education and we often have to get information from governemt websites. I usually have to do it manually but this is so useful. Much appreciated.

[@ashley-tg6eh]

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

[@vivekvenkatram2038]

amazing man...!!! how could u wrap the whole thing in 7+ min... superb.... great job... you are an extreme talent. Best wishes.

[@mybibusiness2560]

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

[@brucew6343]

i only stumbled upon this video by chance but I'm glad I did. Good tutorial, thank you

[@bunrithheng4783]

Thank you so much, I've been desperately looking for this kind of tutorial for weeks. Thank you!!

[@keashanjayaweera]

Thank You. I was tired before googling this

[@diegoduarte4563]

Thank you very much, this explanation was tremendous helpful to me.

[@loquitodan]

I learned a lot from your tutorial, thanks!

[@danschwindenhammer2812]

I would really like to see a tutorial such as putting a Stock Symbol in a cell to lookup the stock price off of yahoo.com. Also awesome tutorial, helped a bunch!

[@zilchsr]

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

[@lokelly2842]

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".

[@andreisweet7642]

You sir are brilliant!

[@imutumpai]

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

[@ubellubo]

Excellent video. Just what I needed to learn next. Cheers.

[@rlbrook1]

Hi DontFretBrett Awesome code! I love the way it works exactly for what I need! Is there a way to get it to work Google Chrome instead of IE?

[@amitshah1922]

You can achieve that with Selenium and VBA combo which will let you use Chrome,IE,Firefox,Opera,Mozilla too. It classified as a automation tool that facilitates scraping of information from the HTML web pages to perform web scraping utilizing google Chrome and etc.

[@razandy]

You know its just crazy how you come across stuff. I have been looking for an alternative solution to Excel Web Quires to get info in from eBay and have been really, really struggling. I had given up and somehow I can across you video. You are amazing I thought I was good on Excel VBA but you make me look like a beginner! Will this work with Username/Password websites like eBay? Have you any experience with eBay tables (Sold Listings-Active Listing) and is it possible to use eBay API in Excel.

[@lordterrin]

Absolutely wonderful video. Thanks man!

[@DontFretBrett]

Thanks! What tutorial should I do next?

[@zohan1ify]

hey can i know your linkedin id?

[@me2this1]

Thank you very much for the small and nice tutorial. It was much helpful.

[@alexclark319]

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

[@aryesegal1988]

THANKS A TON!!! I simply can't thank you enough, that was great, looked for such a tutorial for a long time :] thank you once again, keep 'em coming PLEASE..! :)

[@danielgpratidya]

trully an expert in explaining through video, Thanks a lot :)

[@SSS-hm4pw]

Excellent code. So cool.Thanks.

[@AaronKabasobokwe]

Hi,thanks for sharing! Quick question, What if the data you require is nested within div tags after dd tags?

[@allabout1135]

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

[@manismaithani8375]

Can i get marksheets from website in to excel worksheet according to roll numbers. Plese help me

[@SunSstha]

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?

[@megam0n941]

The part where you altered the url with the text box name (3:10 minute mark). What search words can I use to research it further? Trying to figure out how to fill check boxes and control a tabindex.

[@dmr450]

Great tutorial. Very clear and precise. Thanks!

[@manim1840]

Excellent sir, this vedio... same like that i need for delivery status from the website, How i will get the use consignment no. to get the status...

[@Pgordons]

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...

[@MMinecrafTT]

@MMinecrafTT yea i use vb 2011 and if i do this time1.tick sendkeys.send("#") and i set the timer speed < 0.2 then sometimes when sendkeys.send = enter it starts opening data...

[@AbhishekPatel-qe9xu]

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

[@josephdaquila2479]

I'm not sure the internet controls reference still exists

[@ShawnOConnor350]

what about a website with login credentials for like my inventory sheets i have in excel and i want to auto update them by pulling from the site i use. i already have the xml sheets and stuff just need help with the coding

[@fernandocuba5652]

hello but in your code you have a problem with: sdd = Trim(Doc.getElementsByTagName ("dd") (1). innerText) you could tell me which is the correct form...

[@briocon7]

@pursarth I had the same issue. But when i changed the 1 to a 0 in the following code: sDD = Doc.getElementsByTagName("dd")(0).innerText It started working for me. But my message box gets filled with every state in America. Not too certain why but something is different between the computer he's using and the one's we're using. When i right click i can't get to the "Inspect Element" he mentions in the video. Maybe its a Google Chrome thing.

[@stealthwolf1]

You sir are amazing. If I wanted to loop the search down a column and input the data across the row of the source cell in the column how would I do that?

[@DominicPowell92]

This was very helpful, thank you!

[@rafaelmasilang6490]

First of all, this is EXTREMELY helpful, but what if the website needs you to log in first before you can look up data? Will it still work if I just make sure I'm already logged in before I run the code?

[@dondon1]

Thank you DontFretBrett! This is very helpful. :)

[@sureshkumars5474]

Thanks for this very helpful video.

[@hasanmougharbel8030]

Hey there, god bless your efforts. I am still learning about sql by my own and having today a simple question. I learned that Processes (also known as “procedures”) may take values and give out parameters. Are processes opposite of a function? Is there any related functions used to execute a process? Thanks..

[@gauravkhiwasara]

Thanks for this video. Its very helpful

[@mrheydu]

How can you do the same process from a website that you need to be logged into in order to get the information? is that even possible?

[@raxxx2004]

how can I go directly to "contact us" webpage by clicking the URL listed in an excel?

[@jeanchartier1474]

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

[@吳百正]

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

[@michael-beck]

You're a genius. Great coding. :)

[@tylerjohnmartinez]

hey brett, lets say i wanted to pull data for a list of 100 zipcodes, do you have any suggestions for how to populate county city state if i want to input more than 1 zipcode at a time? thanks for the help! great video!

[@RFMaroja]

Very helpful, solved my problem!!!!!

[@1marc1967]

Great video, very useful, you are a very good teacher. I have a question, i need to do the reverse of your video-take data from excel and input that on a webpage. Such as on a webpage signup you need to enter name address phone number...Is that possible?

[@surfbreakn]

what if the info you need is in javascript?

[@stevelanders7013]

Hi Brett, My VBA wont let me dim as htmldocument. It doesn't seem to recognise htmldocument and doesnt even show it in the dropdown list. Is there a substitiue i can use or do you know why it is not working. Am using Office 2007. thanks

[@sjraghu1982]

Hi Brett, thanks for posting this video. I am able to fire up IE and the MsgBox. However, I am having trouble with the getElementsByName part. Every time I run it, I get a "Run-time error '91': Object variable or With block variable not set" error. What am I missing? Thanks again for the video - learnt a lot!

[@lukster79]

Great tutorial. Could you run this on multiple zip code lines though? Sorry perhaps it's a basic question but I have no experience in visual basic.

[@dividendwatcher]

simply brilliant, good job

[@DevilHackWorld]

Hi, i love your tutorial. Thank you so much...!!!

[@MrEduardoToscano2]

Hi Can I do this with USPS tracking system?

[@DPaquette]

yes of course you can.

[@Yuvastanza]

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

[@abuabou7679]

when right click on the insect element in the search , dnt find the name of the search instead place holder in website screener.com

[@clyburn209]

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?

[@jagatrandhawa938]

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

[@mp1158]

Can you do one, where you have a secure website and need to login? Also how should you address the problem if your search returns several lists/links that you need to go to in order to get the necessary data?

[@welcometothewasteland]

Well done, excellent tutorial

[@solilogram]

Great informative and applicable video - I wonder if it's possible to apply what we've learnt from this video to other websites like Amazon? I realize when using Inspect Element on the first search result of Amazon (eg. Catcher in the rye) I get a bunch of nested DIVs and I have no idea how to access the one I want, in this case the A that contains the title "The Catcher in the Rye" under the H3 tag. In your video, because DD is just under the Body tag, there's no such problem. any idea?

[@MercuryPhoenix2006]

i dont know why in my case the vba show error in the line Set Doc=IE.Document error number 13 type not matched =\ i need this project to my work

[@corporatelifecoach]

Please help me with a web portal that needs user name and password and the data in the portal needs to be scanned like every 2 hours can u help please

[@jamesbailey7299]

That was very interesting. I am a computer tech and have been setting up a company with pc's. They asked me if I could pull phone numbers from the internet and I have spent days trying to find something. Is that possible and how long would it take me to learn VB and excel or what would you charge to create it for me. thank you for taking the time to teach us.

[@faresar]

now i wanna learn VBA!

[@ahrorkuldashev9603]

Great video! Could you also create a similar video about downloading files from websites. Say, you enter a date range and download a excel file with transactions within the given date range.

[@nikhilsharda6147]

what will happen if i enter a wrong zip code, and how to avoid that unresponsive state

[@philipoliveros3839]

how about if ur going to pull out the zip code in different cells. not only in b2 but also in c2, d2, f2, etc. so that its like a table and no need to transfer ito a table

[@mohanraj-vx7gk]

i want to pull data based on the pan number .. example : if i enter a pan in one cell i want to display name in other cell ... and its a gov website ,,,, its possible ?

[@wessamnasser8693]

Fantastic Tutorial!

[@TheAybab]

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

[@Creative-Engineering]

Great video!Your tutorials are easy to understand. I'm trying to import stocks fundamentals from uk.advfn website as well as create charts from price histories from yahoo finance I can do this already,however i would like to do this using a simple drop down menu in excel so i do not have to create a new excel sheet for every company i wish to look at.I have an example on my computer (which i can email you if you like).Thanks

[@ThePurelutz]

why does the first line of your code "And _" causes create an error for me?

[@razandy]

Hi Bret me Again. I have managed to get info from my eBay Active Listing but I cant get the columns separated. It seems after about V 3rd columns all the data is all in one long line. I would post my code on here but there is not enough room. I am pulling the info in by: Trim(doc.getElementsByClassName("dt-rs dt-cs")(0).innerText) from eBay active listings. Hope U can give a little help. Maybe the eBay API would be the best approach but I have no idea! Cheers Ryan UK

[@ernestasraudys2968]

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

[@souandrepeixoto]

Parabéns pela aula, porém eu gostaria de saber como faço para colocar esses códigos no Excel em português? Aguardo ansiosamente pela resposta.

[@anthonysarno321]

Great tutorial, I feel like I am understanding more now. However, I have a scenario where I have a url that is modified by about 6 cells that change its destination. The result is an xml, csv, or json output that I would like to extract data from. I've tried many different ways with no results to extract the data. Maybe if I email it, you could help me.

[@trandaiduong188]

GREAT; GREAT; GREAT. Very useful. Thanks man :)

[@93969pradeep]

Thanks for this great vedio Can you please let me know how to get suggestions for properties(ie document)? even after pressing ctrl+space nothing comes up(like getElement properties)

[@TeamTrav.]

Can you run lookups on multiple variables. I am doing something similar with a different site, but I want it to lookup the first variable, say price and then the second variable say shipping price that is down a little further on the page. Can I have 2 or three sets of this on the vba code: sDD = Trim(doc.getElementsByClassName("result")(0).innerText)

[@rcanseco27]

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?

[@shlerTHEnumbas]

Will you make another video like this for a different website? It would be great if you could explain how to fill in information in the forms of the website. That is what I am struggling with most. I cant seem to click the damn buttons LOL. Thanks again for teaching us!!

[@chrisklest1238]

I have a script that logs me into a website, but once I'm inside I cannot seem to navigate inside the website.

[@Raindeux]

Idk if VBA has the capabilities to do that, but VB itself can click anywhere on the screen you tell it to and perform key clicks. This can be further automated by looking for keywords in the html before determining what to type in to each available text box. Very useful for spam submitting resumes for computer engineering jobs lol.

[@BG-jv3fn]

Anyone know if it's possible to use a web query to get KZbin views cleanly into a sheet?

[@philipoliveros3839]

and what if there's a lot of div each with different id. do you count the divs or insert the id too?

[@-simon-3442]

Sweet thank you so much bro

[@ringh93]

@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?

[@hellmutmatheus2626]

dude you rock