Excel VBA Introduction Part 47.1 - Browsing to Websites and Scraping a Web Page

welcome to this wise L excel vba tutorial in this video we're going to look at a technique referred to as scraping web pages which really is a case of using VBA to open up a web page read its HTML and then get the useful bits of information out of the HTML into a readable format we're going to start by looking at how to use Internet Explorer to browse to a web page and then we can inspect the HTML of that page using a couple of useful techniques of the browser will mention briefly the document object model which is the sort of standardized way that web pages are built and show you where you can find out some useful references for how that works but we start looking at how to use code to refer to the various HTML elements on the page which lets us manipulate the page so we'll do things like fill in text boxes and input boxes on our web page and then identify buttons that we can click on and try to follow hyperlinks once we've done all that for gonna have a really quick look at a simple technique using URL query strings which involves concatenating the address of the website you want to visit but passing in various different values of parameters which affects the results the page returns not all websites allow you to do that but we're going to use an example a really good example of one that does rather than using Internet Explorer there's another useful sort of more efficient technique for getting HTML returned to your VBA code it's case for using something called an H xmlhttprequest and that will become clear when we look at that technique in the video essentially it's a way to avoid having to open up a browser and wait for the page to load it's a much more efficient quicker technique for the final parts of video we're going to look at the actual scraping part I suppose we're going to be looping over various elements of an HTML page specifically focusing on looping cover tables rows and cells so I'll have lots of nested for each loops looping through various elements of a currency exchange rate website we'll wrap up the video by looking at a quick simple user interface using a basic user form which gives a drop-down list for us to select a currency enter an amount and then return a set of exchange rate tables to various different worksheets so it's quite a long detailed video hope you wrote for this one let's get started we'll start the video by looking at how to get Internet Explorer just to browse to a simple website so I'm beginning with a brand new blank Excel workbook the only thing I've done so far is saved it as a macro enabled workbook so from there I can head into the Developer tab of the ribbon choose visual basic and we'll start a brand new module just to get this to work the first ability we're going to create in here I'm going to call something like browse to site if we want to control Internet Explorer we'll need some kind of variable which can hold a reference to the application this is a lot like the technique we use to control Microsoft Word or PowerPoint in previous videos and you might remember from those videos on Word and PowerPoint etc that there were two different ways you could control an application you can use a technique called early binding or a technique called late binding just to very quickly cover though the difference between those two techniques if I was using late binding all I would need to do is start by declaring a variable which can hold a reference to any generic object so the type of the variable will be object what we can then do is set that variable to refer to the result of the create object function in VBA I can say create object and then open some parentheses and then I need to state the name of the class of object that I'm trying to create so in this case what I'm trying to do is create something that refers to an Internet Explorer which I can just about spell dot application what I can then do is control that application by using ie dot but sadly I don't see any intellisense I don't get any help here whatsoever because technically speaking the ie variable could contain any object so you have to know what properties and methods the application has so just to show you a couple of very very basic ones just to get started I know that I can make the Internet Explorer application visible so I can change its visible property to true and I can also make it navigate to a page so I'm going to say navigate and then pass in a simple URL so let's go for let's go for wise out of humans so www.weiu.net in that in every single video ok so having just done that I can execute the the code by pressing f5 and we ought to see a simple Internet Explorer window popping up brow to the Wisel homepage now although late binding works is not necessarily the best technique when you're first learning how to use an application so let's just close down that instance of Internet Explorer and then let's show you how you can use early binding instead so early binding requires you to set a reference to an object library so in this case what we're going to have to do is head to the Tools menu and then choose references and the particular library that we're looking for in here is called Microsoft Internet controls there's quite a long list of libraries mmm as I say remember this from previous videos in the series so let's just find out to composite there it is Microsoft Internet controls very important that we check the box next to that library so then I can click OK and what I can do now is rather than referring to the Institute Explorer as an object I can reference it as a specific class now if you'd like to see which new classes and methods and properties you have access to having referenced this library the simplest thing to do is head into the object browser so unitedly up from the View menu and choose object browser or indeed just press the f2 key on your keyboard using the drop-down list at the top of that window you can select the new library that you've just set a reference to now it's name isn't that obvious in this list it's such a called SH doc VW I'm not quite sure what that abbreviation is for but anyway but I choose SH doc VW this gives me a list of all the classes that are available to me from that library and the one we're really interested in here is one called Internet Explorer so if I select that we can see it's got a list of methods and properties and this will help us with the intellisense when we come to start writing code let's just close down the object browser and see how we can manipulate the code now to use this new class the first thing we can do is replace the word object in the variable declaration with a reference to the specific class we want which in this case is Internet Explorer so if I press ctrl in space you'll see that if I look for Internet Explorer it now appears in the list along with all the other classes sometimes it's useful to to precede the name of the class with the name of the library that it belongs to this is particularly important when you've got duplicate classes defined in in multiple libraries that you've referenced so the name of the other library is called SH doc VW as we've just seen then I enter a full stop there that limits the classes presented to me to just those defined in that library so I can say SH doc whew dot internet explorer what I can then do is modify the set statement so rather than using the create object function what I can do instead is say set ie equal to a new instance of and sh t VW dots Internet Explorer if I could spell doc VW Internet Explorer there we go so that essentially really produces the same functionality that we saw in the previous example one massive advantage of this is that when I say I a dot on another line I don't just get old I don't just have to guess what the methods and properties are anymore I get a lovely intellisense they're showing me all the methods and properties off I'm the visible property in there somewhere down towards the bottom of course there it is and I'll find the navigate method as well so the two things that we've just done I also have another opportunity to save a little bit of time in space with my code here as well so rather than having to set a new instance or create a new instance of the application in a separate statement like this I can actually combine this functionality in the variable declaration if I say dim ie as new SH doc view Internet Explorer I don't technically now need to explicitly create a new instance of it at any point this is referred to as an auto instancing variable so it doesn't actually create the new internet explorer in this declaration what it does is it waits until the variable name is used in code and then it checks to see if that variable reference is something yet if it doesn't they automatically new instance as I'm lunch required so you can save a little bit of time and effort when you're using this er this early binding technique anyway having done all that and hopefully you're happy with the difference between early binding and late binding just to prove that it all works in just the same way the end result will be the same if I run the sub routine I'll end up back on the Y's L homepage now although we're not going to use internet explorer for the entire video it it's worthwhile just seeing a few of the basic things you can make it do so I'm going to close down this instance of Internet Explorer if we want to manipulate a web page once it's loaded it's really important that we wait until Internet Explorer has navigated to the page so whatever code I write after the ie navigate would ordinarily try to take place immediately and that might have happened before the page is finally loaded just to demonstrate that I'm going to write a quick little debug print statement so I can say debug print and we're going to say ie dot location name sorry ie rather mighty but pardon ie lot location name for that comma and then ie dots the location URL who we're going to try to print out two bits of information about the page we just navigated to I'll need to display the immediate window which I can do from view immediate window we'll just press ctrl + G and then if I were to execute the code what we should see is at the bottom of the screen in the immediate window we ought to see the values that I've requested but you can clearly see that it hasn't printed anything if I close down Internet explorer nothing has appeared there at all so to make Internet Explorer wait until the page is finished loading we're going to add a couple of lines of code in between navigating to the page and then trying to do something with it so a common approach to doing this is you can either a do until or a do-while loop I'm going to do while loop what we're going to do here is test the ready state of the Internet Explorer application so as they do while ie dot already state is not equal to then there's five different states that Internet Explorer can be in I'm going to choose the one that says ready state complete so I'm going to carry a looping round until also break button while the the ready state is not complete now you can write some lines of code inside this loop but it's not actually necessary common things you'll see a thing like application dot wait so you make it wait until a particular time of day the common approach to say now plus a time value of one so one second that would add one second to the time right now another thing you would do is use do events to what the literature says that yields the the control to the system to make the system do anything that it need to do but you don't technically need to do any of those things it's perfectly sufficient just to her just have the loop going round testing that condition once that has been met then the code will continue so having done that all I'm going to do now is run the application again run submitting again and we'll see this time that we do actually print out some information about the webpage ok now that we've got a web page successfully loaded let's look at a couple of the very basic things we can make Internet Explorer do to it just to demonstrate this technique I'm going to change the website that web browsing to I'm going to use Wikipedia it's just a simple little example so I've actually already browsed to Wikipedia using Google Chrome so I'm just going to head back there I'm going to copy the URL from the address bar so I select the entire URL and copy that just in case you were wondering why I'm not using Chrome I'm using Internet Explorer for this demonstration is simply because Chrome and Firefox and other browsers don't provide a VBA object library to use there are some third-party tools you can use to make chrome and firefox etc work but I've never used them myself so Internet Explorer is reasonably reliable but we're not going to be using Internet Explorer for the rest of this for most of the rest of this video we'll show you another technique that means you can avoid using web browsers all together but we'll get onto that a little bit later on anyway having copied the URL for Wikipedia I'm going to paste that in in place of wise owl and just to quickly mention because we're using Internet Explorer here it doesn't really matter if you haven't got fully formed URLs so when you type in websites in the address bar in internal for your web browser you don't tend to find yourself writing HTTP etc so because we're using it into Explorer there's validation in place that will add all the necessary prefixes so you don't really need to write fully formed URLs okay let's just test that one works by running the subroutine and having done that you can see down the bottom we printed out some information about Wikipedia now the reason I'm using Wikipedia is because it provides us with a nice little opportunity to manipulate the page using VBA code what we're going to do is get our VBA to write a search phrase in the search box and then click the Go button to search for whatever we've typed in now that does require knowing at least a little bit about the structure of the page fortunately every web browser lets you see the HTML code that makes up the page simply by right-clicking in its background somewhere and then choosing the options similar to view source it might be slightly differently in different browsers so I choose view source in Internet Explorer it gives me a right um up section at the bottom of the page showing me all the HTML code same sort of things in chrome just to just to make sure you're happy with it so if I right-click on the background of the chrome page and then save you page source in this case I'll get a slightly different way so he put actually takes me to a separate page altogether separate tab in chrome now picking through all the code in here is a little bit tricky to do so rather than try to work out exactly where the the code for that this particular search box is located what we can also do if I switch back to Internet Explorer what I can also is actually right-click on a single specific item in the page and choose to inspect element so what that will do in this case again in Internet Explorer it takes me to the exact same panel at the bottom of the page and it takes me to this Dom Explorer so Dom is short for document object model which is a standardized way of building page or referring to the items in a page so what we're actually going to do is we're going to make Wikipedia search for the document object model now the important thing about having inspected this element is it takes us to the exact placing code where that element is defined so you can see that for this input box this text box it's got a tag here called inputs that's that type of object that it is it's got a specific name attribute called search that's also part of a form so this form is another tag which has an ID of search form so what we're going to do is write some code to change the value property of the sir element of the search form form in the entire document so let's just close down or sit in that let's just switch back to into our VB editor and we'll add a little bit of code in after we've printed out the location and URL of the page to start with of course we'll need to reference the internet explorer application and then inside there we're going to refer to the document property and the document property simply reverse to the HTML document that builds up the web page now sadly at this point the intellisense breaks down a little bit if I type in a full stop I don't get any further help whatsoever we're going to see a much more sophisticated way to inspect the HTML of a web page we're going to use a document object model to to sort of really get into the detail of a page later on in the video but for now just as this first simple example you can kind of treat items on the page as though they were VBA collections so much in there was a collection of form objects which we could legitimately refer to as forms inside there we could refer to a form by name and I've just found out that the name of that form was search form that's what we saw in the inspect element option in Internet Explorer so within there a form has got a bunch of sub elements so we've got own elements collection inside the form and one of those was simply called search so that was the name of the input box that we could type our search phrase into inside there we've got a property called value and again without knowing be in debt without having the intellisense you just kind of have to trust that this is the case and then we can make that equal to and then in some double quotes let's search for document object model okay so what I should do is fill in the value of that search box on the Wikipedia page I'm just going to close down the currently open Wikipedia page I'm going to close down the incidence of of Internet Explorer and then I'm going to execute the code just to see if this works so there we go we've got the page we opened up and it's now got document object model typed into that search box at the top the next thing to do is work out how to click that button so again if I right click on that button and I choose inspect element what we should see is it takes us to the exact part of the code that defines what that item is so it's another part of the same form so you see it's a form ID search form but within that form we've got another nested element to name is go so knowing that if I just close down this this instance of Internet Explorer I'm just going to essentially copy and paste most of this line here so I'm going to copy all of that line that refers to the individual items maybe a with block would be a sensible thing to use here but I'm not interested in the search element this time I'm interested in the go element what I'm going to do in this case is rather than try to change its value I'm going to apply a method to it so I know that I can apply a click method to certain objects on the page ok so having done that at this point I'm going to run this one one more time and then we should see that Wikipedia opens up it types a search phrase into the box and then click the Go button to browse to it it is probably worthwhile having a bit of a read of this page at some point I'm not stressing right now I'm not going to go through everything that's on here but it gives you a nice bit of background information about the techniques we're going to use going forwards in this video and there are lots of nice links as well to other pages that will give you more detailed breakdowns of how the document object model works so feel free to have a look at the references of them on the page and the external links have a quick read of those at some point maybe in your spare time ok so at this point I'm just going to close down that instance of Internet Explorer and we're going to go a little bit further and see how we can actually get references to the document itself rather than having to rely on predicting what the individual names of the items on the page are so before we do this let's have a separate example I'm just going to copy and paste essentially the entire subroutine that have already got and then I'm going to paste it in into let's say a new module just to keep things nice and neat and tidy I'm going to insert a new module and then I'll paste that subroutine in I'm going to change the name of the subroutine this time to something like get HTML document and then we'll change a few little things here as well I'm going to clean up the immediate window first of all so I'm going to click in there press ctrl a and then deletes to get rid of it will use the immediate window again shortly and then I'm going to get rid of a few unnecessary bits of this code so I don't need to see the the explicit set ie to a new instance we've already got that a lot of instancing variable we don't need the debug print statement here and we don't need the the bit about the about changing the values of various elements on the page on where those lines as well what I want to do next is get a reference to the the web page that were browsing to as an actual HTML document rather than the basic document property of Internet Explorer now just to make sure that we get as much help as possible with this we're going to set a reference to another object library so let's head up to the Tools menu and choose references again and then if we scroll down far enough what we're looking for this time is some Google Microsoft HTML object library so let's make sure I don't go pass it there it is Microsoft HTML object library make sure you check the box next to that and then click OK if you want to get an idea as to how much or how many classes are defined in this library have a quick look in the View menu and choose object browser again and then change the drop-down list at the top to read MS HTML that's the name of the library we've just referenced there's a huge number of different classes listed in this library it's a pretty sophisticated tool we're of course not going to go through every single last item in here in this video there would last forever we're going to go through enough of these just to give you a starting point for manipulating HTML documents let's just close down the object browser and let's write a little bit of new code now to get a reference to the HTML document what we'll do first is declare a variable which can hold the HTML document we're going to retrieve that say something like dim HTML doc as ms HTML dot HTML document okay having done that what we can do is once the web page is loaded and Internet Explorer is ready we can say set HTML doc equals ie dot document so that stores a reference to the document in that new variable now this is useful because it gives us much finer control over the items in the HTML document just to get a bit of a clue I say HTML dot dot you get an idea of how many methods and properties we've got access to in here so we can get references to items in the page there's a whole bunch of methods to do with getting things getting elements which we'll look out fairly shortly and all sorts of other useful methods and properties that we're going to work with in other parts of this video so just to demonstrate why this is potentially useful I'm going to get rid of that little line there and I'm going to change the web page that will browse to so I'm going to go back to the wise owl web page in fact let's change the URL back to wise owl code at UK notice here I'm missing out the www part as we said the Internet Explorer handles the missing parts of a URL so having done that I'm going to run the subroutine to browse to wise owl what I'd like to do is a similar thing to what we did with Wikipedia I want to be able to type something into this search box and then click the Go button but we've got a little bit of an issue with this if I right click on that search box and choose inspect element what we'll see this time is that the input in here although it has a name is called what isn't it's part of a form which doesn't have a name so I haven't got any easy convenient way to refer to the elements of this page like I did with Wikipedia I can't say forms for named elements element name so what going to do is that is use some of the HTML library's techniques for referencing that specific object by its ideal by its name close down that instance of Internet Explorer and then we'll write a little bit of code to get a reference to it let's declare a variable that's going to hold a reference that search box so I'm going to declare a new variable at the top which I'm going to call let's call it HTML input spell HTML correctly first of all HTML input and I'll declare this as an MS HTML a dot I HTML element so an IH tml element allows us to hold a reference to any individual item on an entire webpage in an entire HTML document having done that I'm going to set my HTML input down to the bottom set HTML input equal to HTML dog dot and then what I'm going to do is use one of its methods to get an item by its ID so the method in here called gets element by ID or by name so if I say get element by ID and then open some parentheses and then open some double quotes as we've just seen the name of that element on the webpage in there in the web page of sauce when we inspected the element was called what so I don't close the double quotes and then close the parentheses what I can now do is change the value of that object so I can say HTML input dot value equals only less search for less that's your Excel VBA and that's what we're that's what we're dealing with okay so having done all of that let's run the subroutine just by pressing f5 and we should see that we've now typed in Excel VBA into that search box now the next thing I want to be able to do is click on the Go button but we've got another problem with this as well if I right-click on the Go button and choose to inspect the element we'll see that when the code does finally pop up in the document object model Explorer then we'll see that although it definitely is a button needs either the class off sorry the M the tag is a button it doesn't actually have a name or ID attribute so that gives us a little bit of an issue what we're going to do because we can't reference it by ID as it doesn't have one we're going to reference it by is tag so the first thing we're going to do is try to return a collection of all of the buttons on the page and then loop through those buttons to see which ones have belonged to the collection and see if we can identify this specific one so there are a few few unique bits of information about this button since it says class name type and value so maybe we can use those instead so having done having established that we want to do that let's close down this instance of Internet Explorer the first thing we need to do is declare a variable which can hold multiple HTML elements so I'm going to call this one dim HTML let's call it buttons as an MS HTML dot I HTML element collection so rather than type all that in let's just scroll through the list so an element collection can hold of course multiple items so having done that let's scroll down a bit further and we can say but let me just tidy up my code a little bit just to get myself a little bit more space what I'm going to say next is set HTML buttons equals HTML doc dot get elements by tag name so tags are things like buttons and divs and etc etc we'll get our tag names by a button and that will give us a reference to all the buttons on the page what we now need to do is establish which buttons belong to the collection to see if we can identify the one we want so to do that I'm going to declare another variable up at the top of the page of course and which is going to be dim HTML M button so just a single button this time so that's going to be an MS HTML dot sorry ms HTML that was meant to be dot I HTML element such as a single one and then just like any other collection of objects in VBA you can loop over those using a for each loop I'm going to say for each HTML button in HTML buttons close off the lip by saying next HTML button and then inside there let's debug put in some useful information about the items in it so I'm going to say let's say debug dot print let's say things like HTML button dot class name so that was one of the attributes of the bun we could also say HTML dot what else could we print out so I say tag name just approve we are printing out information about buttons we can say sorry HTML button dot tag name so that should only of course be button because that's the type of objects where we've got access to we could try to print out the ID of the object but we know that it doesn't have one so we can say HTML button dot ID but we know that that doesn't exist another potentially useful thing we could do is say HTML button dot in a text so the inner text I'll show you what that is in just a moment but if I say inner text and then let's just have a look at what that prints out if I run the code we'll see Internet Explorer pops up and we get a list of all of the buttons on the page now the immediate window shows is there's all the information about a single button that's been printed so if I just go back to the web browser and right click on the button that we were trying to get access to and inspect the element when the code opens up you can see that there it is so its class name that we printed out is search underscore underscore submit and then the class that'll show you the tag name must have been button because that's all we've asked for the ID part is empty we know that it doesn't have an ID or a name attribute and the inner text part has been written out as well it says go so the you note text part is if you can see here inside the start tag and the end tag of the HTML button these nearly got the text there go so that indicates what's displayed on the button now you might be looking at that and thinking well why is it only printed information about one single button when it looks like there's lots and lots of buttons on the page these things all look like buttons clearly um kind of impossible to tell just by looking at a webpage without inspecting the source what that actual item is although it looks like a button if I right click and choose inspect element you'll see that it's actually not a button tag at all you can see that what's been highlighted is an a tag now a tags refer to hyperlinks basically so essentially all this is is just some fancy formatted hyperlink text sitting on the page is not a button after all it has got a class name button alpha my um who knows why it's called button alpha I didn't design the website but um so the class name indicates that it's it's meant to be a type of button but it's actual tag name definitely isn't so that actually makes our life a lot easier if we know that there's only a single item in the buttons collection here we can reference it by index number so all collections in VB ofcourse are indexed and it just so happens that the index is for HTML element collections begin at zero so if I know that I've only got one item in that collection I can say HTML buttons open some parentheses the index of this will be zero then I can apply the click method to it okay I can comment out the for each loop as well I don't need to do that now that I've established how many buttons I've got so all I should need to do now is execute the code again and I'll end up with Excel VBA typed in and then I've clicked the button to see the search results for Excel VBA okay so that one was fairly easy because we found out that there was only one button on the page so he just reference it by his index number what if there was a slightly more complicated page so I've actually browse to an already I'm just going to close down Internet explorer for the moment and then let's head back to Chrome and I've got a page up here one that I used in the previous video actually when we were using query tables to get tables of exchange rates so what we've got here is a slightly more complex site with a bunch more different things we can click on what I'd like to do is be able to connect to this site and then fill in some various values in these inputs and then click the correct link or button whatever it turns out to be to go to that specific page so ideally what I want to be able to do is go to the rates table page so I've got some data to pick out so back to the VB editor and again just to tidy up a little bit I'm going to clear out the immediate window first by ctrl a and then delete and then I'm going to make another copy of this entire subroutine and then stick it into a new module so that's going to have a new module and then paste all that in and I'm going to use are changing them with several teens that it's called something like browse to exchange rates so let's just change the code a little bit we we don't want to browse to the wise owl page this time of course we want to browse to a site called X - rates com so I can just type that throughout the in experts.com and I don't know what what elements are on that page yet so let's just get rid of all this code here about clicking buttons and getting elements by ID we may well do something very similar in a moment we definitely want to get a reference to the HTML document though so we'll leave that one in there and we're probably gonna have inputs to type in Deus I'm going to leave that variable in there and let's just leave the buttons in there for the time being as well at this point let's just make sure that part works we can browse the page and see what's in there in Internet Explorer okay so let's see what inputs were working with first of all I'm going to right click on the input that let me type in the amount I'm trying to convert and choose inspect element and once the dama Explorer has got to the correct part we can see that it's actually got a name it's going to name and an ID both called amount so that's good news means we can reference out that item directly by getting element by ID let's do the same thing for the currency we're converting from I can right click and choose inspect element and that one tells me it hasn't got a name it doesn't look like oh but it has got an ID so that one says from so we can use again get element by ID if we were going to change the other currency we're converting to as well then we could find out what that one is image is right click and choose inspect element and find that that one is called it's got an ID of two so that's good news who it means we've got unique names for all these things we got amount from and two so let's deal with filling in those things first of all let's close down Internet Explorer and work out how we get references to each of those inputs so we'll use the variable we've already declared up here HTML input so let's say set HTML input equals HTML doc dot and get element by ID and then we know that it's name was called amount so that's a nice simple one to get we can then say HTML input dots value equals and let's just prove that it will change let's set something slightly different let's say I don't know let's say five for example then effectively we can just repeat that for the other two inputs in case we wanted to change them so let's copy and paste those two lines of code and I'm going to change the next one so that it's next ideas from so that's the currency we're converting from now we have to be slightly careful about this because on the website itself is a set of drop-down lists so choosing from the drop-down lists means we have to be slightly careful about what values we put in so the currencies were allowed to use our three-letter codes for the currencies so I think within US dollars to begin with let's put it into GBP instead okay and then the next thing just to prove that it will work we may well not end up using this one but if I just copy those two lines again and paste it in again excuse me sorry missed that copy that and then paste it in again and then change the input of the IDE to the two element and then in this case will change from GBP to let's say let's say I don't USD let's go to US dollars okay just to see if all that works let's just hit f5 to execute the code and make sure that when everything's finished and the page is finally loaded we've got all the inputs changed to the values you've asked wells you've got five GBP and USD okay the next thing we have to do is work out how to click the appropriate button what I'd like to be able to do is see the rates tables for the particular inputs so automate that work I'm going to right click on the item and I'm going to choose to inspect the element when the document object model finally loads and points me to the right place what we can see here is that it's not a button just like we saw on the white cell page it's not a button it's an a tag this it's just a basic hyperlink now this is likely that we're going to need lots and lots of links on the same page lots and lots of a tags so we're gonna have to do a little bit more work here let's see what we can investigate so we can loop over all the hyperlink tags or all the a tags first of all and we've got a couple of attributes we've got on click which tells us the what basically what code is triggered when somebody clicks on it and we've got an href which is the actual URL that it'll browse to and L sorry well relative so it's some sort of a relative attribute of rates table so let's see let's first of all let's deal with looping over all the hyperlink tags all the a tags first of all I'm just going to close down the instance of Internet Explorer and then let's just change some of our variables we've got HTML buttons and button let's call this HTML ace and HTML a so we'll use the tag names which makes it look a little bit silly but there we go HTML a is an HTML a okay so the next thing we need to do is get a reference to the collection of a tags so after we've filled in the various inputs let's have a look let's say set HTML a x' equals HTML doc dot and gets elements skewing get elements by tag name so in this case a tag names nice and easy the tag name is just a and again having done that we can write a simple for each loop so we can say for each HTML a in HTML A's hit next and also hit next HTML a back one and what I want to be able to do is print out some useful information so let's say debug print and let's say I know what we'll do here actually were interested in the attributes of the of the a tags those are the things that give us a most useful information so we're going to do is say HTML a dot and there's a method here called get attribute so the attributes as we saw these little names the way in red text inside the tags so we could do things like print out the class name class name is one of the attributes so we could say class name we could also say HTML a dot gets attribute the two that we were really interested in were hate ref that was the name of the the link that it points see or they the HTML so back button the URL that it was pointing to and the other one was HTML a dots get attribute opens in parenthesis in quotes and the other ones called rel okay so having done all of that let's find out what happens if we execute the code and see how many items we get printed out eventually it will pop up so it's quite a few in this case I'm just going to close down the the web page at that point and have a quick look at what we've got so all the hyperlinks you can imagine that page is full of all sorts of hyperlinks so lots and lots and lots of rows here hopefully if we scroll up far enough to those towards the top it's kind of a good clue here I think that these are the very top menu bar in the page so those little things in the top menu bar scrolling down a little bit further we got an amount you're there oh we've got some things there now these are looking a little bit more familiar so it looks like many of these a tags don't actually have a class name some of them do but some of them don't the mdh ref and the rel it looks like most of them have got an H ref on a rail suddenly everything's got nature F so we can see that we've got this H ref to x-rays comm table and race table which is as rel attribute that gives us a clue about how we can actually get access to that individual specific hyperlink so although we're not going to be able to reference it by ID or by name we do have at least have some kind of inversion that can give us access to it one last quick thing I forgot to mention just in case you wanted to find out how many items there were in a particular collection I'm gonna add a quick little debug print actually to the end of my loop that's going to say debug dot print HTML A's dot length so you want to know how many items there are if I just run this one again we'll see that we get the same list of things spit out but eventually at the bottom it will point out it would print out how many items there were so 102 different hyperlinks okay so having a salvage all that let's work out how we can get or drill into this specific one that we're interested in okay so let's just tidy up a tiny little bits here I'm going to get rid of that last debug print statement I've just added and then what I'm going to do is write a couple of lines of extra code inside the for each loop so we'll still will still loop over all of the attributes sorry all of the a tags but for each one we're going to check if it's H ref and rel attributes match the link which I'm gonna click on so essentially we're just going to write a basic if statement so I'm gonna start by saying if then the first thing I'll check is if the href attribute which I'll just copy that and then paste it in I don't to check if that is equal to and then if I scroll up in my immediate window I ought to be able to find the one that I was looking for so rather than having to type it all out and risk getting it wrong this is on the rows after so when the href is equal to x rays com4 slash table so i copy that out and paste it inside a set of now just to make absolutely sure I'm going to check the rel attribute as well so I'm going to say and and I'm going to copy and paste this part and when the rel attribute is equal to and then once again to avoid miss typing it I'm going to copy and paste rates table so in some double quotes paste that in and then at the end of that say then okay so it's quite a long winded line of code let me just see if I can change the width of my screen a little bit so you can see it a little more clearly what's happening there we goes there's a full if statement let's wrap that up with an end if and then inside there what I basically want to do is if that is the correct link that I'm looking for then I want to click it so I can say HTML a dot click just like we did for the button in the wise our website example last of all I don't want to don't continue looping through all of the other tags all of the other a tags so that point I'm going to say exit for now hopefully having done all of that if I just comment out maybe the debug print statements to avoid printing it all out again let's have one last go at running this just to check that it works so hit f5 to run it and we'll see that all the values get filled in and it has indeed followed the link to get to the next page you can see that it's actually blanked out the from a so pick run the two inputs that wasn't actually important when you view the rates tables it shows you lots and lots of different currencies on the same page so we can probably just get rid of this or in fact we can of course get rid of that input what we're changing the to so at that point let's close down internet explorer and let's see what we can do next okay so we've seen a little bit about how we can get Internet Explorer to control the web page you browse to but in many cases you'll probably find that you don't actually have to go to this amount of trouble this particular site we brought you here the yet crazy com has a really nice convenient way for us to get a particular list of currency exchange rates for a particular currency in a particular amount I just run this code again just so we can browse to that page we shot this technique actually used in the previous video when we were using query tables if you can have a look at the address bar here look at the URL you'll see that it's got a question mark in it indicating the start of a query string so after the question mark is if a couple of named parameters as a from parameter which is equal to GPP and an amount parameter equal to the number five now because of a URL in terms of VBA manipulating it in VBA is just a string you can concatenate that string out as various component parts so rather than having to browse to the home page and then work out how to fill in the form and click on the button we could have actually just navigated to that specific page in the first place so let's have a quick go at doing that I'm going to copy this entire subroutine again at this point just for the sake of convenience copy that to the clipboard and then insert a new module and then paste it all in and then let's rename it so we can say browser exchange exchange rates with query string now we can actually get rid of a lot of the code in this particular subroutine so let's scroll down a little bit and we can get rid of all the parts where we set a reference to the various inputs and then change their values likewise for getting a reference to all the a tags and then looping over them we don't need any of that at all let's just get rid of all of that and then we can modify the URL so at traits comm /table /dataarray the two parameters there were from and we can make that equal to let's say let's say GBP followed by a ampersand which will allow us to add on another parameter and we can make the amount parameter equal to let's just do something like that just so you see it is different let's say three and will essentially achieve the same result as what we did with a much more complex example previously so that means we can get rid of a lot of these a lot of these variables as well so we don't need to have the a tags and the collection or indeed the input so let's get rid of all that as well that's a much much simpler way to get reference to a particular HTML document I just execute this just to prove that it does indeed work we end up on a page with all the exchange rates tables for GPP GBP sorry I'm with an amount of three much much simpler now of course not every website is constructed in the same way not every website users query strings so if you try to do the same thing with a wise owl website and passed in a query string using Excel VBA as a search base that simply wouldn't work so there are reasons why you might need to use Internet Explorer but if you can navigate to a web page and you can get to a page that exactly the page you want using query strings then there's actually no reason to use Internet Explorer at all there's a much more efficient way that avoids the complexity and the overhead involved in running a separate application and waiting for it to browse to a page so what we're going to do is have a look at using another object library which allows us to connect to a web page retrieve a set of results without having to open up a separate application at all now I'm actually going to use the exact same subroutine that I've just created here I'm going to copy and paste it into the same module this time I think just to avoid creating lots and lots of different modules and let me just close down the immediate window for the time being as well I need to modify the the subroutine name I can't have two support teams with the same name in the same scopes let's call this with query string and XML so that gives you a clue about to about what we're about to do we're going to reference another object library if we head back to the Tools menu and choose references and then if you scroll down far enough you're going to find a list of references called Microsoft XML and you should find this a variety of these available some slightly more old fashioned slightly more sort of out-of-date ones and the most modern one that I've got access to is version 6 so technically you should go with their most recent version that's on your machine unless you're supporting legacy application in this case I'm going to check the one for version sex and then click OK so this XML library lets us get a set of results from a webpage without having to go via a web browser so what that means is I can get rid of my variable which refers to Internet Explorer and I can replace it with a completely new variable what you call this let's call this one let's call it let's call it XML page I guess I can't think of a much better name for it than that and the type of object that we going to store in there is an MS XML - that's the middle library we've just referenced and specifically an XML HTTP in this case it's 66 0 6.00 first the particular version of XML that I'm using now if you'd referenced a previous version of the object library you might find that you don't have access of course of the 6.0 version in which case it might just be XML HTTP or it might be XML HTTP 50 so it might be slightly different depending on which library you've referenced in my case is definitely 60 and if you wanted to check yourself which one you were using then the best thing to do is head to the View menu and choose object browser and then look for the MS XML to library if you scroll down far enough to the the class that starts with MS XML HTTP you ought to find similar in there so there we go XML HTTP 60 in my case so you might find that's got a slightly different name in your own particular version I'm just going to close down the object browser and then look at what are the changes we need to make to this code to get that page what I'm going to do next is convert both these variables into Auto instancing variables so to avoid having to create new instances explicitly I'm going to rely on those to do that for me themselves so at the new keyword tweeted the variable declarations now of course because we're not opening Internet Explorer there's absolutely no reason to try to make Internet Explorer visible so let's get rid of that instruction and then the URL part the way that the part that this comes into play is when we try to open up this a XML HTTP 60 connection so rather than Aida gate what we're going to do is say XML page dot open now there are several parameters to fill in here the URL is actually the second parameter so before we do that we've got to say what method we're trying to apply to this the particular method to get a set of results is simply called get but you have to enter that as a string so in a set of double quotes enter the word get followed by a comma then you pass in the URL which we've already got there and then one last thing that is worthwhile doing there's a third it's an optional parameter var async so this is the asynchronous property of this connection if we set this to false what that will do is run the connection synchronously which means that essentially everything else will wait until the page is loaded is almost the equivalent of waiting for Internet Explorer's ready state which in this case of course we don't need to do so we can get rid of that loop as well what we then need to do is we need to send that request to to the X on page so I'm gonna say XML page dot send that sends the request we've just built and then we need to wait for the response now when we get the response what we want to do is set all of the the HTML of our HTML document to be equal to the response text from that XML request so we're going to say HTML doc and dot body dot inner HTML equals so what this will do is create a brand new HTML document so remember we're using an auto instancing variable at this point people not we don't have an HTML document already this will create a brand new one and set its inner HTML property of its body equal to our XML page dot response text so having done that we basically now got to the same point where we're at with the with the Excel internet sorry with the M Internet Explorer example now one important difference of this technique over Internet Explorer is that although Internet Explorer has some validation in place to make sure that the URL is always perfectly formed with this technique that's not always the case if I try to execute this right now I'm going to end up with a runtime error I hit debug it's going to fail on trying to open up that URL let's just stop that one so the simple way to solve that problem is just to make sure that your URL is properly formed so if I say HTTP /o pend colon for slash and then execute it again although we've all seen the results of course at least we don't get any runtime errors now just to make sure that we are getting essentially the same results from this I want to redo it little test that gives us a list of table so it least counts how many tables there are in the page we navigate to so I just use the Internet Explorer version just for the moment and then just show you what what I'm after in this case there are a couple of different tables in this page if I just right-click on one of them and choose inspect element I ought to find somewhere in there when the Dom Explorer star that finally finishes loading I would find that in somewhere in there we've got a table tag so I want to look at all the table tags in the page let me just close that down and then what I'm going to do to make that work is declare a couple of basic variables so let's say dim HTML tables as hate ms HTML and dots I HTML element collection so just as we've done previously and then what I'd like to do is once I've got to the page and I've set a reference to it I'm going to say set HTML tables equals HTML doc dot get elements by tag name and then in this case that tag name excuse me the tag name is table having two nuts all I'd like to do is debug print HTML tables dot length just as we've done before and the nice thing is that I can just copy and paste exactly the same code into my other routines let me just do that quickly before we give it a tip quick test so copy the variable declaration and then the two lines which set a reference and print out the length at that point I'm just going to open up the immediate window so I press ctrl + G and then ctrl a to select it all and then delete just to get rid of it all okay so if I execute the one which involves using Internet Explorer just to see the results will work with patient first to load and then eventually the page will stop loading and print out the number two so two tables altogether let me just close down Internet explorer and just prove that I run this second one we don't see Internet Explorer pop-up of course but if I run it I'll get the number two but hopefully you can tell that runs an awful lot quicker than the Internet Explorer version so if all you're interested in is the actual values of the page the actual HTML of the page you'll turn retrieve then don't go by Internet Explorer this technique is much much quicker so bearing that in mind we're going to move on a little bit now and look at what we can do with the wrist the response text the HTML that's returned from this point forward it doesn't really matter which technique you're using whether using Internet Explorer or the XML technique what we're going to do with the HTML will work regardless okay so the next thing we're going to do is write a subroutine which processes all the tables on the page that we've referenced because I'd like to be able to test this using both of the different techniques we've used so far both with Internet Explorer and XML HTTP requests we're going to write a separate subroutine which accepts a HTML document as a single parameter so we're just going to tidy up the two procedures we've written here so far I'm going to get rid of the variables that refers to the HTML tables and then we're going to get rid of the two lines which set and then debug some information about that collection we'll do that in both of the procedures then what I'm going to do is head over to another new module just to keep all the codes separate again it's going to insert one new module and in there I'm going to create a new subroutine so this one's going to be called a process HTML page and we're going to have a single parameter in here called HTML page as an MS HTML dot HTML document okay so that procedure will accept a single parameter and we can call that one now from each of the two procedures that we've just finished writing okay so the first thing we're going to do is process all of the tables on the document that we passed into this subroutine and we'll use the exact same technique we've used to process all the hyperlinks and all the buttons in the previous examples so let's start by declaring a variable which I'm going to call HTML table as an MS HTML dot HTML element then we'll need the corresponding variable which will hold the collection so in this case dim HTML tables as MS HTML dot HTML element collection I'll scroll through the lists you're going to get so bored of writing this if you're doing this a lot in the real world okay so the next job is to get a reference to the tables collection and we're going to do that is by saying set HTML tables equals HTML page dot and get elements by tag name so just as we've done previously we going to get elements by tag name and I'm going to refer to the table tag okay so now we can loop over those tables using a symbol for each loop so we can say for each HTML table in HTML tables so if I use the intellisense to tell me out without say being a bit of typing and plotting ly to say next HTML table and inside there all we're going to do is print out some information about each table so we can use a simple debug top print statement we will actually get around to writing out some useful information into some worksheet shortly but for now I just want to debug top print HTML table dot class name so I'm look for the air or the circular HTML table dot class name I'm going to put HTML page there HTML table dot class name so having done that I'm just going to go back to module number 4 and make a call to this process of HTML page so I'm going to go to module number 4 and in each one of these in each one of these subroutines so using Internet Explorer and the XML example I'm going to say process HTML page and into that I'm going to pass in this HTML doc variable so HTML doc for each one of these just copy/paste exactly the same code into the other procedure as well okay I'm going to run this one first using Internet Explorer just because I'd like to be able to leave the web page open so we can see what we're doing so I just click back into the Internet Explorer example and run that one using the f5 key and lend up with that website to display it and it's printed out two names for the two tables involved in that page just to prove that the same thing will work whether we do it with the XML version so as you can just click into that procedure and run that one as well and I'll get the exact same two class names but the beauty of that is I didn't have to wait for internet explorer to fire up and of course although the web page with all this stupid adverts to load etc etc so having done that the next job is to start looking into these tables and work out what elements we can extract from them so back to inspecting elements in our webpage let me just right-click somewhere inside this table and choose inspect element now this is probably going to turn up quite a lot of code so I've looks like a right clicked on one of the header cells one that has the word British pounds in it so you can see that I've got us an item here and a tag called th which is refers to a table header that refers to a single cell in the table header row that's enclosed within a TR tag which opens a table row and that's enclosed within the tea head tag which of course correspond to the table header all of that's contained within the rates table table so you can see that clearly corresponds to what we've printed out here already now there's also a table body tag if I expand the table body that corresponds to all the individual rows in that table and each one of those is represented by that by another TR tag so inside there we've got individual TD tags with a standard table row rather than in a header where they call th tags in a standard row they're called TD tags but again you'd hopefully see there for each TD tag it corresponds to what information is shown in that cell so what what I'd like to do now is write some code that will loop over all of the various elements of each table unlike this data out into a new Excel worksheet so I'm just going to leave this this Internet Explorer incidence open for the moment just so we can refer back to this if required let's head back to the VB editor and head back into module number five to work out what extra code we need to write to loop through the table rows we can use pretty much exactly the same technique we've just used to loop through the tables themselves so we can get the elements by tag name looking for TR tags and then loop through all of the individual HTML elements in that collection so I should have another variable which we'll use to hold a reference to a single row and a dim HTML row as an MS HTML dot HTML element should just copied and pasted that and then inside our original for each loop we'll write another one so we can say for each HTML row in HTML table and then what we're going to do is apply the get elements by tag name method directly to this HTML table element now confusingly the intellisense doesn't show us that that's possible sadly it shows that we can get an attribute but not get elements by tag name but we can still each each node in a in an HTML document contains child nodes and you can pretty much apply the same techniques to the main document as you can to each individual node certainly in this case we're getting elements by tag name so that the intellisense doesn't include on that that we can undo this we can happily get elements by tag name in fact what I'm going to do is just copy and paste from the previous example I'm going to say get elements by tag name and then simply change that so it says TR so I can close off the loop then by saying next HTML row and what I'm going to do then is debug print let's say a tab character so I say debug dot print a VB tab as well as the inner text of that row so I'm going to say HTML Rho dot inner text so having done that let's just get this one another quick test I'm going to clear out the contents of the immediate window first head back to module number 4 and then I'm going to run my XML version just to make it quicker so hopefully we can see there that we have indeed printed out each individual row so we've got two tables rights table first and then indented one one tab space we've got each individual row of that table then it moves on to the next table and just exactly the same thing for that table as well now the next job is to start separating each row into its individual cells so I said back to module number five and as you can probably imagine we're going to use pretty much exactly the same technique again and letter for each loop to loop through all of the elements in each row so let's start by declaring another variable which I'm going to say dim HTML cell as another ms/ms HTML HTML element I'm getting bored of saying it never mind typing it so let's just copy and paste from that line so let's have another quick go we go back into our loop that loops over the rows we can have another for each loop so I'm going to say for each HTML cell in now we can use exactly the same technique as we've just looked at to return the rows in each table I can refer to HTML row which is another element and then I can apply the get elements by tag name seems they get elements by tag name even though the intellisense doesn't show me that I can do that to me in fact to avoid miss typing it let me just copy and paste and then we just need to update the tag that we're testing for in this case I'm going to go for the TD tag which is going to cause us a small issue as you'll see shortly but for each HTML cell in in the in the the rows cells and when you say next HTML cell I'm just going to once again debug dot print let's say a VB tab and the HTML cell and dot in a text okay so I'm just going to take away this debug print statement to avoid printing out the same information more than once and then let's just go back to module number four and give that one a quick test so if I run the XML version again just to make sure it runs as quickly as possible I should clear out the contents of the immediate window again and then I can run this XML version one more time and we'll see this time we get each piece of information separated out by self so not just an entire row printed out each individual cells value printer out now there is one small problem with what we've done in this example because we looked for explicitly TD tags what we're missing from this list is any reference to the table header cells if I can browse it far enough hopefully you can see here the table sorter rates table we start with the first row of data the first TD cell which is Argentine peso so if I quickly look back at the currency table and have a scroll down to that one you can see that's exactly what we got so we've missed out the header rows and I quite like to include those now it is possible to loop through the or we could use the get elements by tag name and look for the th tags but that's going to be a little bit inconvenient means we've got to do two separate loops one slip over the th tags and then another ones a lip of the TD tags so what we're going to do instead is just modify which collection we're looping over back in module number five rather than printing out or looping through all of the elements whose tag is TD what we're going to do instead is simply loop over all of the children at the table row so this is a completely different collection here you see there's a children property of an HTML element so thinking about this idea that HTML documents are made up of a series of nested tags then you can imagine that any child of the row will be any tag that sits directly inside it so if we just look back at the Dom Explorer you can see that for a table row we've got in this case in the header row the children are th tags and then for the data rows you can see that the children of the TR are the TD tags instead so this means that we can avoid this little problem of having to loop over separately the th tags and the TD tags so simply having made that change if I switch back to the code and then go back into module number four again again I'm going to clear the contents of the immediate window and then just run the XML version one more time and this time if I scroll up again far enough why all to see I don't think I've got quite enough space to print out everything in the immediate window it does have a limit but hopefully here you can see look I've got tables sort of rate stable and it's printing out the header row so British found three GBP and one GBP so that corresponds to the head of shown here so that's kind of a nice convenient little technique to use to print out or loop through the children of any element in an HTML document okay so all that remains now is to take the information from the immediate window and start writing it out into you worksheet cells so here's what I'd like to achieve back in module number five for each table that we loop over I'd like to create a completely separate worksheet for each one and then on each worksheet I'd like to print out first of all a quick little header which includes the class name of the table and the date/time stamp and then the header row from the table and then every single row of data from that table so let's just clear up the immediate winner in fact I'm just going to close the immediate window down for the time being because we've got all the loops to do the hard work here all that we really need to do now is work out how to get that printed into the correct elements in the worksheets so to start with what we all need to do is for each table that we loop over we'll need to create a new worksheet so that's relatively straightforward we've done this in many videos in the past I'm going to say worksheets dot add there's lots of ways you can control exactly where the worksheet appears as we've talked about in previous videos you've got before and after parameter which you can use to specify exactly where the worksheet appears I'm just going to accept that the sheet appears wherever it wants to basically to the left of whichever what sheet is currently active when the code runs then what I'm going to do is rather than debug print HTML table class name I'm going to set range a ones value equal to HTML tables dark lastname so when you add a new worksheet that sheet becomes the active sheet so you can refer to range a1 on that sheet and it will let will allow us to set its value I'd also like to say range a b1 and dot value equals now that will give us a date and time stamp showing us exactly when the data was printed that's kind of an important thing because unlike in the previous video when we were using query tables that can refresh and bring in the most recent data the technique we're using here prints a fixed set of values so it's great for getting historical data and maintaining that historical data not so great if you want the live like we had in the previous video now what we need to do is work out which cell in the worksheet the data from the table will go into so let's declare a couple of extra variables just to help out with that I'm going to have dim romanum as long comma cold numb as integer then what I'm going to do is once I've created a new worksheet and I've printed out some data into row number one so cell a1 and b1 I'm going to set the Ronin parameter or sorry the Ronin burial to be equal to the number two I need to do a similar thing before I start looping over each of the cells in the row so before I begin that for each loop I'm going to say Col num equals one so that's that as soon as I'm going to start putting my data into column a in the worksheet then what I'm going to do is rather than debug print the information from the cell I'm going to use the cells property of the worksheet so I'm going to say cells open some parentheses and then I'm going to say Ronin so passing in the index number of the row that I want to set the value of and then a comma and then the column will of course be the Col num variable so cells romanum column equals HTML cell dots in a text what I've then got to do is before I move on to the next cell in the row I want to increase the column number so to do that I can say column equals calm them plus 1 3 let me go spell that correctly column plus 1 finally just before we move on to the next row I want to do a similar thing to increase the row count so I can say just before I move on to the next row I want to say row num equals row num plus 1 okay so apart from a little bit of formatting and tidying up that's pretty much the core part of the code rhythm let's head back into module number four and let's give this one a quick test using the XML version the quick version so I just run this one now we want to see a couple of new worksheets appear and if I switch back into Excel I were to find that I've got one sheet containing the data for all the currencies just widen the columns a little bit I could have written code to do this of course and formatted the headers I switch back on to sheet two we've got the data from the top ten rates table so there we go so all that's working kind of nicely if we wanted to do a bit of tidying up and sort of getting these columns to to be the correct width etc and formatting all that's relatively trivial stuff and we've covered that in a lot of previous videos so I don't want to go into all that sort of basic formatting stuff again at this stage what I am interested in doing however is making our code a little bit more flexible and dynamic similar to the technique we use in the previous video where we were able to modify the query string that we were using so rather than always getting this exact currency in this exact amount making that part variable using some basic user inputs now there are lots of different techniques I could use to get some user input which will set the currency and the amount so I could just use simple input boxes which I've used in many of the previous videos in the series asking the user for a currency in an amount that's a little bit flaky is least easy to control what the user does particularly for a list of currency so it should be a fixed list they shouldn't just be a lot of typing whatever they want we could do as you did in the previous video whereby we set up some cells on the worksheet so we reserved a couple of cells and use some basic data validation techniques to create a drop-down list of currencies and then validated another cell which would only accept numbers that weren't negative those techniques worked and we've seen that already in the previous video hopefully watch that one another technique and probably the best technique in the long run will be to create some kind of user form that would be the easiest one to control and the easiest one to validate as well what we'll do just to start with though is set a perversion of this procedure that we can call and then pass into it the currency and the amount we want so I'm just going to create an copy this XML version of the subroutine which connects to the X rates website eggplant just hit the wrong Joker key their cursor control-c to copy and then I can insert another new module what into it I'm going to paste this procedure I will change its name as well so I'm just gonna call it get exchange rates and then take away there with query string and XML part and then I'm going to declare a couple of simple parameters so the first one is going to be called from currency as the string followed by another comma and then I'm going to say amount as let's say what we have this one as this one can be a double okay so having declared those two what will then do is modify the URL to be built up of all the different parts we've passed in so to help with that I'm going to declare a new variable which I'm going to simply call URL as a string and then I'm going to say URL equals and start building up the string out of the various component parts so the first part will be equal to this first part of our URL let me just copy that part and place it into a set of double quotes then from that I would like to concatenate the value of the from currency parameter so you can say from currency and then from that I would like to concatenate another string of text so that's picking up from this part here so ampersand amount equals and then I can paste that into the double quotes and then finally concatenate on to the end of that the value of the amount parameter so what I need to do then at that point is modify the open method so I'm not referring to that fixed string I'm referring to whatever string I've built up in this URL variable so from there all I need to do is create some way to call this procedure passing in a currency and an amount the longest winded way to do this would be using the userform now I've got a whole series of video is about creating user forms so just to run through the absolute basics of how that works let's right-click on the project and choose insert user form and on there what I'd like to do is generate a combo box which is the fancy name for a drop-down list and then also a simple text box into which I can type an amount so I can just use do that using the tool box I can I'm not going to go into any real detail here about creating user forms as an entire series all about this or kind of assuming you know at least a little bit about forms at this point I'm going to give these objects sensible names so rather than this just being called combo box one and you're going to modify his name slightly so I'm going to call it one we're going to call it I'm going to call it currency and then I can call it that because it's a data type called currency of course so let's call it from currency that correct there we go and then the text box I'm gonna put in there it's simply going to be called amount okay then what I'll need is a button that I can click on to run the code so I'm going to go back to the tool box and find a command button and draw one of those somewhere and I'm going to call this command button something like get rates I can change its caption there as well so it says something more useful rather than command but mana can be easy and say get rates and I could also change the caption of the form itself so I can change its name first of all so I've got an easier way to to reference it so I can call it rates form or rates form there we go and I can change its caption there as well so that it says something like choose rates you'll see that changing in the title bar up at the top of the form now I need of course a sensible way to launch this form as well so what I'll do then is head back into module number 6 and I'm going to create a simple little subroutine that's going to be called open rates form and then what I'm going to do is have a simple line code that says rate form not show so that'll make it appear on screen now what I can do is somewhere in my workbook so getting very long-winded this process I said it was quite said it was the longest when the most tedious way to do it I'm going to pick sheet one as my basic user interface head on to the développé Developer tab in the ribbon head into the insert tool and draw a basic form controls button so I draw one of those on the web sorry on the worksheet I can choose the open rates form macro as it's called or subroutine click OK and then I'll just change the size of the button a little bit edit the text on there so that it says something like and gets rates okay so at that point if I just click away from the button not just zoom in a little bit on there on the screen and then click the get rates button it will open up the form so the couple of extra things I've got to do now I've got to populate this drop-down list with some sensible values and then a bit of validation as well for the textbox to make sure that I type in a number and not a negative number and hook all of that up to the click event of the get rates button to run all the code I'm just going to close a form down at that point head back to the VB editor and then let's go back to the design of the form which I'm already there let's deal with populating this drop-down this first of all there are quite a few different ways to do that one simple way is to add items to the combo box when the form is first loaded so a quick simple way to get to the events of the form is just to double click on its background if I do that I'll be taken to the code view of the form I've got slightly the wrong event I've got the click event when the event that I really want if I use the drop down this at the top right hand corner is the initialize event so I select initialize that'll generate the correct event handler I can then just get rid of the click event writing code to populate the combobox is pretty straightforward all I need to do is refer to the from currency drop-down list and then say dot add item then all I've got to do in there is specify exactly what item I want to add in so let's say let's put GBP in there first everything else is essentially just a repeat of that so however many currencies I want to get as long as I know what their names are from the website I can add them to the list so in fact if we if we can add a few let's have a simple with a block here some say with from currency and then say dots pad item I'll drop in the end with and then I can simply copy and paste this a few times so let's sorry that keyboard shortcuts not quite working again there we go copy and paste and then we can change the currency let's have a USD and we can say what else we have we can have you are 4 euros we could have JPY as well it's kind of important here of course to match the text with the app which will acceptable value from the website so at this point you'll probably want to have a quick check you can see all the little abbreviations which are relevant here so let's have Australian dollars and Canadian dollars as well shall we say so we can add in a couple more sets AUD and another one which is C ad okay so that will populate the drop-down list the next thing we've got to do is make sure that when we double click though sorry when we click on the button it does it a little bit of validation to make sure we've entered a number into the amount box so let's head back into the design view the form you can do that by just double clicking and then if we double click on the button that'll take us to the correct event handler all the default event handler for the click event of that button now the validation code we're going to add here is very similar to the stuff we did in the example from the previous video so what I'm going to do in here it's a simple if statement that will say if not is numeric then I'm going to look for the amount objects that's the text box so I renamed earlier on amount dot value then we'll have a quick little bit of validation code we could display a simple message box most box the amount must be a number excuse me amount must be a number and then we should also check sorry Beckham I want to make sure that I exit the subroutine at that point we should also check I suppose that we have entered a positive number as well we wouldn't want to enter negative numbers in there so we could also just do a quick simple thing that says if let's say ammount about EU is less than or equal to zero then we could say put pop-up with another message box which says the amount must be positive you can add the insult of your choice the end of that's just in case you've got some users who insist on doing the wrong thing all the time and then we say exit sub and then end if again we should also just check that we have actually selected an amount from the combo box as well so we could add another if statement up here we could say if like I'm sorry we can say if from currency dots value equals an empty string then again another simple message box you must pick a currency and then we could exit sub and then end if now annoyingly with combo boxes or maybe this is actually a good fit good feature with combo boxes on user forms by default they're not restricted to the items you've populated the list with so I could legitimately type in anything else in the drop-down list that I like if you wanted to fix that the simplest thing to do is go back to the design view the form select the combo box and then look for the match required property so I scroll down the list up with a match required property you'll see it's currently set to false I change that to true I can then ensure that I only pick about you from that drop-down list so I just want to quickly check that system works so far if I go back to the workbook click get rates and make sure that I click get rates first of all says I must the amount must be a number so I haven't typed anything in yet let's make sure I type in a number let's write timing a negative number it's going to be a positive number okay fine so let's put in I know three then let's click get rates it says I must be currency so of course I haven't done that so I can't just type in whatever I want if I try to do that says that's invalid I must pick one from the list so I got GBP three click get rates and nothing will happen yet of course but that just ensures that the validation part works okay let's close the form down head back to the VB editor and we'll just took up the last tiny little bit of code in the get rates click event the great thing for us now is that all we have to do at this point is call our get exchange rates procedure so I can say get exchange rates and then I've got to pass in two values so the first value has got to be the currency that I've selected from the drop-down list so I can say what they call the drop-down list it was from currency wasn't it so from currency dots value followed by a comma and the not going to do then is passing the amount as well and that's coming from the amount text box as amount dots from value okay so having just done that let's just head back into Excel and we'll give it a final quick little test I'm going to choose get rates I'm going to go for let's go for your roses time and I'm going to go for a value of two then click get rates and we ought to see that in the background we end up with tables for the euros or two euros we're just close the form down so I can look at that data I get the same table names time stamps euros - I've got another sheet there that's got the basic top ten table as well few so it's a little bit of effort to to work with and of course we haven't done all the formatting stuff yet either which I'm not going to do in this particular video I'm sure you can work out you the formatting from various other things we've done that's kind of the trivial part the important thing is we've got a really nice useful system now that queries a web page in the most efficient way possible without opening up Internet Explorer of course I've left that one open from earlier on and gets your table of data paste it into some worksheet cells by looping over the various element in the web and the web page so I'll it's been quite a long video I hope you've got some news out of some of those features and hope you can adapt that to your own needs hope you found it useful thanks for watching see you next time if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 229,248

Rating: 4.9540229 out of 5

Keywords: excel vba, visual basic for applications, html, mshtml, xml, xmlhttp, web scraping, scraping websites, html document, document object model, dom, wise owl, internet explorer, browse to website, navigate, website

Id: dShR33CdlY8

Length: 86min 0sec (5160 seconds)

Published: Sun Nov 13 2016