Excel VBA Pull Data From A Website

how's it going in this tutorial I'm going to show you how to make a city state and county lookup when the user enters a zip code so to start out let's get a zip code field county city and state and I'm going to put some main ranges and column B so they're easy to refer to in code the formulas tab click on define name this one's going to be zip code click on the one next to county that one's going to be county city and state I want my program to fire off anytime someone enters a zip code in here and the way we'll capture that is by going into visual basics clicking double-clicking on the sheet that you're working in click on worksheet in the left drop down and on the right drop down where the events are we're going to go to change this fires off any time the cell has changed we want to make sure it's the zip code cell so if the target row equals range zip code that row and target column equals range zip code column then by the way the range names here are not case-sensitive it's just a habit of mine and probably a best practice that you keep your casing consistent so we know that the row is in in the zip code row and the column is in the zip code column so we're ready to fire off this code I'm going to use an Internet Explorer window to go to a website and pull the rest of the information that we need off so we need a reference to that object go to tools references click on the list and type em to get down to your M's might take a while by scrolling we're going to need a Microsoft Internet controls right there and a Microsoft HTML object library I'm going to declare a variable for the new Internet Explorer window dim ie as new Internet Explorer ie dot visible equals true in the final product you won't want your browser I'd be visible it looks much cleaner if it just runs in the background they don't even see it but for the tutorial will be nice to have it open so you can see what it's doing next thing we need is a website to pull the information off of if you type County look up it'll bring up a lot of different websites and the one I'm going to use in this tutorial is quick facts census gov wait works is you enter a zip code and it will return a city a county and a state city county state if you right click in this text box here in google chrome you have the option to inspect the element which will give us some details about that text box and here I can see that the name of the textbox is place so instead of navigating to the home page of this and then typing in or putting text in there with code and then submitting this request with our code we can actually skip that step by navigating directly to our search result come up to the URL bar and type a question mark place equals and then your zip code and hit enter and it will bring up the same page so in our code we're going to want to copy this up until the equals we need to iead on navigate and after that we're going to want the zip code from the Excel worksheet range zip code dot value and we need to wait till this browser is done we can do that by loop until I that ready state equals complete and if you don't know about do events that just prevents the program from freezing up while it's waiting on you if you don't put that in there the program doesn't accept mouse clicks or keyboard entries so it's not responsive next thing I'm going to want to do is set a variable to hold the document object of the internet explorer which in contains the information that we want to access so damn doc as HTML document set the document to IAS document and we need to look at the HTML of the website to find out where this information is housed so come back and grow and chrome excuse me and right-click where says Sacramento inspect element and if you look in the list here there's GD tags and there's two in this case the second one happens to have our information the city the county and the state now that we know that let's create a string STD and string s CD equals ia dot sorry dot dot get elements by tag name DD and it wasn't the first one but it was the second one and in code world the first one is actually zero and the second one is one so we're going to want one and we're going to want the inner text which will taking out all the HTML stuff for us also you're going to want to trim this because I believe there were some leading spaces and trailing spaces let's try this I'll do a message box and see what the result is close this browser code window let's try 92108 or something so if you go back to excel I got a message box with my our information you got the city the county and the state so we need to break those three up and put them in the in the cells one way to break those up would be using split function so I'm going to do another variable tin a DD as variant 82 equal split STD you're going to split it by each of those words by a comma and a space which will set it to a DD 0 will be the city a TD 1 will be the County a DD 2 would be the state and the way we just put them in the cells would be range I was a college county dot value equals a DD 1 range City value equals a TD zero range state value equals a TD 2 after we get the inner text of the DD let's go ahead and close the browser window I'm also going to get rid of this or comment out the visible lines so we don't even see the browser window open let's try running this now see how it looks there we have it typed in a zip code and I got the County the city and the state let's try another one there we have it Harris County Houston Texas and that is how you pull information into Excel from a website thanks for watching

Channel: DontFretBrett

Views: 520,722

Rating: 4.9376345 out of 5

Keywords: excel, vba, macro, microsoft, automation, vb, vb6, vb.net, net, visual, studio, visual studio, tutorial, guide, demonstration, lesson, mvp, city, state, county, lookup, look, up

Id: 7sZRcaaAVbg

Length: 7min 15sec (435 seconds)

Published: Fri Dec 10 2010