Excel VBA Pull Data From A Website
HTML-код
- Опубликовано: 30 сен 2024
- 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...
One of my students gave me the link to your video. Excellent stuff. Thank you!
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?
YOU ARE GENIUS
Bro DontFretBrett....Very nice to see your tutorial,
I am Following every step in your tutorial, but i got error at
Doc.getElementsByTagName("dd")
Can you tell what is missing?
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.
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.
I would use:
If Target.address = Range("zipCode").address Then
...
End If
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?
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
Hi,thanks for sharing!
Quick question, What if the data you require is nested within div tags after dd tags?
i dont have dd tags but i have id tags for example
is there a way i can extract this
I was wondering if you'd be willing to help me with something very similar to your video. I'm needing VBA to scrape table data from a excel column of URLs that are from the same website. Each URL would return 5 results from a column within the table that's being scraped. Also, I would need a time delay between each URL query to avoid throttling.
If you could add me as a contact I'd love to give you my table html and the excel sheet I'm working on so you could have a look.
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
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.
@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.
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)
Brett,
I'm getting an error when my code is word for word the same as yours........ Amazing skills btw. How'd you learn VBA?
I'm trying to click. A button on a web site but am unable to do so
The HTML code is
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
Hi,
I want to write a code to pull information from my gmail account into excel and paste to columns with headings. The information in gmail account comes with the same format starting with 'Ship to:' then Name, Address, Phone Number, etc. Can you please help me writing this code?
Thank you so much in advance for your help.
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...
@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...
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
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
Good day.
I tried completing this task, but I kept getting the type mismatch error message for the line IE.navigate. Can this error be corrected? Please advise.
Hi Brett, i am very new in that kind of stuff. I am trying to pull down information whether one email address is verified or not via the website tools.verifyemailaddress.io/ ... With your tutorial i cant do this, because it is not possible to come to a result by using ...?EmailAdress=... in the url. Do you have a solution for me for this?
Hi, excellent tutorial, is there no problem, could you please send me the excel file so I can adjust it to my needs?
Thanks and regrads!!
Diana
Hey great video. Is there a way to reverse it:
where for eg. on the website, people will type a number and a message will popup. The message will be save against the number on excel. Is it doable? Thanks.
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.
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...
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...
Thank you very much, this explanation was tremendous helpful to me.
Can i get marksheets from website in to excel worksheet according to roll numbers. Plese help me
This is a great video! You taught me a bunch of stuff I didn't even know to ask for.
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!
amazing man...!!! how could u wrap the whole thing in 7+ min... superb.... great job... you are an extreme talent. Best wishes.
Hi Can I do this with USPS tracking system?
yes of course you can.
You sir are brilliant!
when right click on the insect element in the search , dnt find the name of the search instead place holder in website screener.com
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
I learned a lot from your tutorial, thanks!
How I get mail ID ,phone number ,country, zipcode ,city,districk, from Google ,
you haxxor le peeps
I'm not sure the internet controls reference still exists
amazing, how to use with MS Edge
Thanks! What tutorial should I do next?
hey can i know your linkedin id?
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.
sorry sir... may you upload file source code
Can you please make more examples, please!!!
May i get file source code sir?? Please
"If Target.Address = Range("zipCode").Address Then " would be better ;)
You hiring?
It really a very good video, I need some help, how does it can be done, if I have enter in zip code column it has to retrieve the details from the site , like street and city. For example, house number , street and city, postal code are all columns. when I enter a postal code in the column corresponding row must be updated with the address, would appreciate if you can suggest the way.
DontFretBrett Great tutorial. I, in fact, am hiring. How do we get in touch?
Excellent code. So cool.Thanks.
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..
Does this work with the new Edge browser?
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?
what if the info you need is in javascript?
god almighty are you from nasa or something
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
Thank You. I was tired before googling this
It's country, not county? Nice tip thanks
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?
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 video - I'm almost there. I get the same error mentioned by a few of the viewers.
sdd = Trim(Doc.getElementsByTagName ("dd") (1). innerText) --> this gives me run time error 91 (object not set etc.)
If I set (1) to (0) as a viewer below suggested, I get all states dumped into the msgbox. any help solving this last bit would be greatly appreciated. Cheers
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
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!
Sweet thank you so much bro
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.
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"
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".
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.
how does he highlight the key words ?
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!
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!
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.
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!
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?
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... :(
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 it is a site and you can try logging in as much as you want, and it doesnt say something like : wait 2 min
is that what you mean with protection or do you mean the database??
Your video is amazing, thank you. I'm trying to replicate it to extract prices from a table without a search. I'd like to limit the field by 1-2 seller's price, whose field name is "div class="num" instead of "dd." Before I simply extracted data from Excel's "get external data > from web" but this seems faster & narrower for numerous entries. Do you help people on VBAs like on Fiverr?
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?
@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?
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.
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
Like scraping views from maybe 20 or so videos into an Excel file, not sure if this is even possible BUT I have seen similar Wizardry with Google Docs!
I know you can pull data like say, income statements rows and columns for financial analysis.
How bout for pulling that same data when the data is in an imbedded pdf forms where you cannot open up the actual pdf that’s posted on the webpage, but all you can do is scroll left and right within the pdf. That format is popular in financial sites currently.
Any way to do this on Excel for Mac 2011 which doesn't have Microsoft Internet Controls or Microsoft HTML Object Library in the References? Also, is it possible to use Chrome instead of IE?
Great vid by the way.
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
Hi, Thank you for the video it was great. I am using your code to get the pronunciation of some words by using define telephone for example in google. The page displays /ˈteləˌfōn/ but when i use your code it is returning /ˈtel??fon/. For some reason it is not returning the special characters as i would like. Please can you help...
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,
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.
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 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.
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
Can you pull data from pdf files?
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 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:))))
is there any chance you could post the full corrected code above or message me it please ? I have written the corrected version of the code but it does not do anything.
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!