Excel VBA Pull Data From A Website
Vložit
- čas přidán 9. 12. 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...
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!
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
One of my students gave me the link to your video. Excellent stuff. Thank you!
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!!!
"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.
Why all the negativity? Was there a purpose to that?
Attempting to educate you on the English language.
you are an idiot, david
@@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.
This is probably the clearest tutorial within 10 minutes i've seen in a loooong time!
Thank you so much, I've been desperately looking for this kind of tutorial for weeks. Thank you!!
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.
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!!
Well done DonFretBrett, this is a great explanation of the power of IE & HTML objects in VBA. Thank you very much.
amazing man...!!! how could u wrap the whole thing in 7+ min... superb.... great job... you are an extreme talent. Best wishes.
you are awesome! never saw such a crystalized tutorial you are absolutelt amazing!
This is a great video! You taught me a bunch of stuff I didn't even know to ask for.
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
Great knowledge & your help is really appreciable!! I like your speed of typing the code also
Thank You. I was tired before googling this
Thank you very much, this explanation was tremendous helpful to me.
Excellent video. Just what I needed to learn next. Cheers.
I learned a lot from your tutorial, thanks!
i only stumbled upon this video by chance but I'm glad I did. Good tutorial, thank you
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..! :)
trully an expert in explaining through video, Thanks a lot :)
Absolutely wonderful video. Thanks man!
Great tutorial. Very clear and precise.
Thanks!
This was very helpful, thank you!
You sir are brilliant!
Hi,thanks for sharing!
Quick question, What if the data you require is nested within div tags after dd tags?
Holy shit, I thought I was pretty good with Excel until now! Great info.
What the knowledge...... man You are VBA God. Thanks!
Thanks for this video.
Its very helpful
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
Thank you very much for the small and nice tutorial. It was much helpful.
Thanks for this very helpful video.
Thank you DontFretBrett! This is very helpful. :)
Hi, i love your tutorial.
Thank you so much...!!!
Excellent code. So cool.Thanks.
it's amazing that it can be done using vba
Well done, excellent tutorial
You're a genius. Great coding. :)
Fantastic Tutorial!
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...
simply brilliant, good job
Thanks! What tutorial should I do next?
hey can i know your linkedin id?
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?
Very helpful, solved my problem!!!!!
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!
awsome video, thank you sooo much.
GREAT; GREAT; GREAT. Very useful. Thanks man :)
this is awesome Good job
Very nice, thanks for uploading
Very impressive!
Funtastic and searching for this crawler video :D. Thanks +DontFretBrett
very nice. pure ownage. you are good!
YOU ARE GENIUS
great tutorial !!
Your VBA Editor looks quite nice. What kind of settings and font do u use? Nice tutorial by the was - very good explained!
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
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!!
I have a script that logs me into a website, but once I'm inside I cannot seem to navigate inside the website.
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.
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
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?
dude you rock
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?
Great job!
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
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.
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..
Excellent!!
Sweet thank you so much bro
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?
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.
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?
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".
I would use:
If Target.address = Range("zipCode").address Then
...
End If
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.
Very cool video!!!
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?
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!
I keep getting "Compile Error: Sub or Function not defined" with my version. What could be causing this? Thanks!
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:))))
now i wanna learn VBA!
Great Video!
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.
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!
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.
Nice one. Thanks a lot.
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!
gr8 Video ! very helpful
thank you for sharing
Is it possible to create a macro code to retrieve content from website and compare it with database data?
Excellent
Appreciate it :)
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
Awesome!
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?
Hi. Is this for US only or can you find Zip codes from any country?
and what if there's a lot of div each with different id. do you count the divs or insert the id too?
This is awesome stuff. What if I want it's a product page on a retail site, in which the URL contains the unique product ID. I would want to for example, insert a URL or an item number into the excel sheet, and then want it to pull the regular price and sale price of the item showing on the product page?
Amazing!
Hi Brett,
Thank you so much for sharing this video and that is great.
I’m working on importing data from web (company intranet) to Excel using vba. The web is made of javascript so there are a lot NOBR tags, such as:
NOBR 6/8/2012 /NOBR BR /DIV
onMouseOver="mouseOver('I4418', 'Net Deposits …
6/8/2012 and Net Deposits is what I need to extract.
Is there MS Java object library that I can reference to? Or how can I import data from this web to excel
Thanks for your help in advance
@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?
loved your video, it was very insightful but my question is, can this be done with open office? libre office?.. and do you know if this can also be done with Microsoft excel 2003 and 2007? thank you! subscribed!
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?