Excel VBA Introduction Part 46 - Querying Web Pages using Query Tables

welcome to this valve excel vba tutorial in this video we're going to have a look at how to query your web page using VP a query tables so the video is all about how to get data from a web page into Excel we'll start with a bit of background information and show you how to use Excel built-in web query tool basically that's to show you how horrific we basic the tool is and why you might want to consider using VBA instead of that tool so we'll show you them how to create a basic query table using VBA connecting to a fairly static basic website as part of the buy/sell website actually then we'll look at how you can set up refresh options so you make sure that the data table refreshes either on a regular basis or when you open up the file and then we'll talk about the various different elements you can import into a query table so you can choose either to import an entire webpage all the tables on the page or just specific tables on the page to help out with that little parts we're going to teach you a tiny little bit of how to read basic HTML so we can look at how to find table tags in the HTML of a web page and also how you can see the HTML of a web page just in case you don't know how to do that the latter half of the video then is going to create a slightly more dynamic example using something called URL query strings so we're going to get the user to pass in different values into the URL to connect to a web page to affect the results returned by it it's quite a nice sort of dynamic example using a set of currency exchange rates and the ability to choose which currency you use and what amount you're trying to convert so - I have fun video this one let's get started before we get started on the VBA side of generating query tables we're going to have a look at a quick simple tool built into Excel that allows you to generate a query table without writing even a single line of code so if nothing else this will give you a bit of a background and insight into what the code we're about to write is going to try to do so we're going to use a tool that's on the data tab of the ribbon now I'm using Excel 2013 in this video it's a brand new completely blank workbook the same tool exists by the way in Excel 2016 and 2010 and I'm pretty sure 2007 as well so whichever version of Excel you're using from 2007 onwards you should have access on the data tab to a button that says from web so what this little tool does is launches a very very basic web browser and when I say basic I really do mean basically I mean it's even worse than Internet Explorer but some what you can do here is attempt to browse to a webpage to identify a table that you want to import into a new excel worksheet now the key to using this tool effectively is finding a good candidate website that has lots of different tables you choose to import now you could technically if you really wanted to talk to yourself use this tool as a basic web browser so I've got Google if I could do legitimate do a Google search let's maybe search for the wise owl page let's search for wise owl training sorry wise owl I'll spell it properly while training and you'll see in there that you get a basic web browser and I can click on the wise owl link and it will browse me to wise our training now all those things have worked pretty smoothly so far that's more to do with the fact that the wise owl website isn't actually all that sophisticated although and you burn the guy who wrote the website won't thank me for saying that so hopefully you will watch this video but this this web browser struggles with many modern websites things with lots of dynamic elements html5 JavaScript etc it tends to fall over quite easily so rather than trying to use this basic browser to find the site you're interested in what it suggests doing is first of all canceling out of that or closing it down and then just using a regular web browser like for instance Google Chrome which I have open here or Firefox or even Internet Explorer or edge or whichever other one you want to use so I've got a couple of web pages open already that I've browse I've got another page on the wires our website which is a list of our courses so just a few basic tables in here and again this is a good candidate for a web query because it's got some nicely organized separate tables of data probably a slightly more real-world use for this this web query tool would be importing something like currency exchange tables so rather than a basic sort of fairly static set of list of courses like these tables are something that dynamically changes on a regular basis like exchange rates so these are good because these have got some lots of different tables available that we can import so if you wanted to use the same sites that I'm using then feel free to just copy the URL you of course you can pause the video and and type that in what I will do just start with is copy the wise owl URL so if the wise owl course I'm going to copy that one first of all and then I'm going to head back to Excel and I'm going to launch my little web tool again so from the data tab in the ribbon choose from web from here then I can simply paste in the URL in the address bar and then I can click the Go button to browse directly to that website so I have to navigate through lots of different pages that might make this tool fall over now the reason as I say I chose this particular page was because it was full of different tables what I'm looking for now is at the top left hand corner of any table I'm looking for one of these little yellow boxes with a black arrow that indicates a separate region of data that this tool can import however if I scroll down to find the individual tables on this page sadly the tools failed to detect them what I should be seeing a little yellow box at the top left hand corner now again this is kind of a bit of a feature of modern websites old moral fashion websites when back in the day when essentially sites were made up of a bunch of HTML table tags and you had tables and tables within tables and so on and so on and so on each individual one of these tables would have a separate box that I could choose to import currently sadly all I've got in this page is essentially a single box that I could use to import the entire page so I'm just going to do that very quickly just to show you how that works I'm going to select that box it goes green with a tick in it now I click the import button got a separate dialog box popping up asking me where I want to import the data to I'm just going to put this on a new worksheet the time being there's a bunch of extra properties that we're going to investigate when we get to the VBA side and at that point I'm just going to click OK so I'll get a brand new worksheet and it's going to try and query the wise owl courses page and because I've just imported the entire page really all I get is a great big long massive list of individual cell values which isn't particularly useful you've got like you can kind of identify the individual tables partway through the page so that's not particularly useful I'm going to just delete that worksheet and go back to sheet 1 choose yes and you want to delete that and I'll show you how the same technique works for the other webpage that I browse too so again just going back to my generic web browser I'm going to head to my currency exchange tables website and again just copy the URL from the address bar once I've done that I can head back into Excel and again choose my from web tool and then just paste in the URL now this is an example of one of those sites has got lots of dynamic content in it which this basic web browser really doesn't like as you're about to see if I hit the Go button I'm going to get presented with eventually a whole bunch of different little arrow boxes popping up as it slows the page it seems to be reasonably happy at this point you often get really really horrible little error messages essentially if you get little error messages popping up then all you need to do is click the notice and I'll ask you things like do you want to try to debug the webpage you definitely don't just keep on clicking no until you run out of error messages and it actually takes you to the to the page so you can hopefully see in this example although I have got a single yellow box at the top left hand corner but let me insert is select the entire page what I've also got our individual tables with all the other box at the top so hovering over the little yellow box gives you an indication of what table of data it's going to import so I'm going to check the box next to this table you can choose more than one at the same time so I might want to choose this one and down here as well so I could choose to import more than one table at the same time I think that's probably enough just for the sake of demonstration then at that point if I click in boards again I'm going to choose to put it on a new worksheet just really briefly the properties give you access to do things like choose whether the table will enable background refresh whether it refreshes on a regular basis whether it refreshes when you open the file again again we'll have a look at all of these properties when we look at the VBA code you can also give it a more sensible name than the one that is pin sort of automatically generates I'm just going to click OK at this point and then click OK again to create a new worksheet with those two tables of data and this should look a little bit more useful so that it is much more easy to see hopefully here that you've got two separate tables imported much better organized again I'm just going to delete that worksheet the whole point of this video is to show you how to do the same thing using VBA and one of the advantages of using VBA is that the VBA technique is much cleverer identifying the different tables in a page even for the wise our courses list where the basic query tool couldn't see the the individual tables we can actually do that using VBA ok so from here let's head into the VB editor I'm going to head to the Developer tab and choose Visual Basic and as ever we'll need to insert a brand new modules let's right click and choose insert module which I'm not going to bother renaming I'm just going to create a quick subroutine which is going to be called sub imports wise owl courses that's the first example we're going to do we're going to use a few basic variables for this so I'm going to start with a simple worksheet variable which I'm gonna say dim WS as worksheet the really important one here is one called QT which I'm going to call QT as a query table so a query table is a type of object that we're generating using the basic query tool I'm also going to have a simple string variable which you can hold the URL of our web page there that the site whether we're importing data from sings a dim URL as string and then the first thing I'm going to do is set the URLs value sometimes a URL equals and I'm just gonna make this equal to the URL of the wise our courses page so I'm going to quickly switch back to that website copy the URL head back to the VB editor and then paste it in inside the double quotes next we're going to create a new worksheet and the reason I'm doing that is because every individual worksheet maintains its own separate collection of query tables so just to keep things sort of separated out in this video I'm gonna have a separate worksheet for each query table that we generate so we say separate ws equals the worksheets dot add and that's a fairly standard set need we've used many times in the course of this video series and then what I'm going to do is add a query table to that worksheet so to do that I'm going to say set Q T equals WS dot query tables dots pad I'm going to open up some parentheses at that point and there's a couple of compulsory parameters we need to fill in which a connection and destination so the connection is the most important thing here if I type in a space underscore just so I can write this in struct across multiple different lines I'm going to say to connection : Eagles I'll use named parameters and then for a URL parameter it's got to be proceeded with URL semicolon so in a set of double quotes URL semicolon followed by an ampersand and then the actual URL that we've stored in this URL variable so here I can just say URL I'm going to type a comma and a space underscore and then the destination we just need to state the top left hand corner so where the data will start being pasted from essentially so I'm set destination equals range a 1 so it's going to start pacing it at the top left-hand corner of the worksheet close a couple of sets of parentheses there and then we're essentially ready to execute the query to execute the query all you really need to do is apply the Refresh method to it so if I say Qt dot or refresh and then that's basically all of the code written I'm just going to restore down the VB editor window at that point and if we we can see Excel in the background there we go and if I just briefly use the f8 key to step through so you can see the URL being set obviously that happens and then a new worksheet gets created and then we create a new query table so it's not this instruction that actually makes the query table appear that just generates the object to actually make it appear in Excel we need to apply the Refresh method so if I hit the f8 key you will see this little query running in the background let make sure I hit f8 to end the sub and you can see that reasonably intelligently actually the query in this case is picked out all the individual tables from the Y's our courses page rather than the entire page which is what we got when we use the basic data from web tool now I'm just going to delete this worksheet and go back to the VP editor because there are lots of other things that we can do to modify the way the query table works back in the VP editor rather than just refreshing the query table I'm going to apply a bunch of different properties to it so I'm actually gonna write a separate thing on the write a width statement because we're going to have so many different properties we can change them say with QT and the very last thing we're going to do is refresh it and let's have an end with there so before we refresh the query table there's a bunch of other properties we'd like to set there are a couple of properties associated with refreshing your query table so obviously we can apply the Refresh method whenever we want to explicitly refresh the table but you can also set it up to refresh automatically so for instance there's one way you can set the query table to refresh automatically whenever you open the file so the property for that is called refresh on file open and you can simply set that to true if you want to refresh the query table each time you open the file we also saw on the dialog box earlier on that you could set the query table to refresh after so many minutes and to do that you could set the refresh period property so refresh period equals now for this property you can set a number with a range of 0 to 32767 and that loudly represents the number of minutes between each refresh so for instance if I so it's 5 it will be 5 minutes before they are between each refresh of the table if you'd set that up previously and you wanted to disable the automatic refresh you'd simply set this value to 0 and that will disable it so in this example I'm not actually going to bother having an automatic refresh I'll have a refresh on file open but I'm certainly not intending to wait around for at least a minute just so that you can see the table will automatically refresh it might without keeping that in there for reference for later on feel free to just comment it out I'm just going to remove that line altogether you can also change the query tables name using the name property so we saw earlier on on the dialog box that query tables are given a default name which is usually based on the range of data being imported those names are particularly useful or readable so you can assign a unique specific for explicit naming to your tables which will help you to identify it so if you ever need to refer to your query table in code later on you can reference it of course as virtually every object in any Excel VBA collection you can reference it by an index number but a query table you might want to reference it by name instead so in this case I'm going to call this query table wall courses wall short for wise owl so there we go there's a name to the table the final property that will change just before we see the effect of all this code is the property that changes the formatting at the web data so there is a property called web formatting so say web formatting and then make that equal to I've got a variety of options here so I can set all non or RTF so the default option here is actually web formatting also the default format you saw earlier on try to bring in all of the formatting from the web page I didn't think that was particularly successful or attractive personally but the move that's just me you could set this to none which means you just get plain text which is what we saw when we imported the data using the basic query tool from the excel data tab I found most success using rich text formatting RTF so that's the technique I'm going to use here feel free to play around with these just so you can see the various different options so having done all that I'm not going to bother stepping through this code this time I'm just going to hit the f5 key or click the green triangle button to play or run the subroutine and this time we should see a slightly different appearance to the data that we import one other thing quickly worthwhile mentioning about this is that the web formatting can change the way hyperlinks work so the rich text format you can see that this is just raw text basically I don't have any hyperlinks in here at all most switch back and I change my web formatting to excel web formatting all which was actually the default if I execute this again although I personally don't think the look of that the text is particularly nice you do actually get the links to there the URLs that those hyperlinks point to so it's a bit of a balancing act I suppose it depends on what's more important to you or data or the links and the functionality I'm just going to delete that page at that point and go back to the rich text formatting I prefer the look of that one so I'm just going to change that one back to RTF so far we've just accepted that whenever we run this tool we get whatever data the query table thing that's appropriate to return so in this case it's returned all three tables from that wise our courses page but you've got a little bit more control over this than you might expect as well so there's another property that determines what data you actually import from the web query so you say or look for the web selection type property you can set this to one of three different things either all tables the entire page or more intriguingly specified tables so at this point we've got all tables from that web page so the way this works is basically it looks for HTML table tag so if I just set this to all tables and run the code again we're going to see exactly the same results so that's clearly the default so this does I try to identify table tags now if you know a little bit about HTML this will make a bit more sense if you don't know a bit about HTML you can still understand a little bit about it if I could take you back to the web browser and have a look at the wise owl courses page and I have a right-click in the background of this page this works in any web browser by the way it doesn't is not unique to Google Chrome you can do this for any web page in any browser right click and view page source what that will do is it will open up in the Google Chrome's case it opens up another tab in the same browser showing you the HTML code sitting behind this web page which is kind of interesting to see is is quite a nice way to find out how web pages are built so if you scroll down a little bit basically just in case you don't know anything about HTML certainly not trying to patch a nice shoe for you if you do know HTML reasonably well but some HTML pages are essentially built of different tags so you see these tags are indicated with the the open angle bracket there and this is a this thing called a div tag invision tag and what I'm really looking for in here is something called a table tag so here I've found a table tag that's the open table tag now if your web developers as written the HTML cleanly they'll have written beautifully indented code to make it easy to identify the corresponding closing table tag so closing tags start with a forward slash so we have the open table tag there everything between those two tags defines one single table so that's what that property that we were just looking at was I was trying to identify so it's he's got one table tag there then there should be another table tag a little bit further down there's another one and it's corresponding clothes is there then a little bit further down again there's another opentable tag and that should be the last one in this page if we found any more table tags then they should have been imported as well but if I kept on scrolling down and down and down there are only three table tags in that document so that hopefully helps you to explain or help speculate in to you why when you say excel all tables that's the set of data that you get so as we saw that was the default selection type for the web query if we change this from excel all tables let's go back and I'm just going to hit ctrl in space to redisplay the intellisense list let's try the excel entire page now this should be more like what we got when we use the basic web browser tool as part of the data tab of the ribbon so I run this one this time what I should get is a bit more information so you can clearly see now we've got all of the other links that were that were imported at the top of the page and sorry if I just scroll down the page little bit you should see the tables do have here rich text formatting means we get a bit more of an interesting output we can see the tables up here as well but it sits alongside all of the other items that are in that page now the final setting for this property rather than entire page or all tables is one called Excel specified tables which is kind of interesting as long as you know how many tables there are then you can specify exactly which tables to bring back by combining this property with a property called web tables so you can set the web tables property equal to a comma-separated list of numbers corresponding to which tables you want to return so for instance if I just wanted to return let's say the third table from my wise our courses that's the one full of although these really nerdy eg programming courses which are quite good fun let's say web tables equals three now it's just one single table you want you can enter this as a number strictly speaking the web tables property is a string property and but you can enter a number and it will be implicitly converted into a string technically speaking it should be entered as a string in double quotes and if you wanted to return more than one table you'd have to write a comma-separated list and that would have to be written inside a set of double quotes so in this case I'm just going to return a table number three just to prove that that one works I'm just going to hit the X key button or the run button and we'll see now be doing deed get just one single table this time if I change this and let's say I want tables one and two I can type in one comma two and then hit f5 again to run this one again and this time I'll just get tables one and two that's kind of a kind of a cool technique to use to control exactly which data you get back from a big webpage so that's all well and good you can choose exactly which tables you want to appear on each page but what if you wanted to simply create a separate web page for every individual table on the webpage let's just tidy up our workbook a little bit first I'm going to head back to excel and I'm going to select the last sheet that I just created which is sheet 16 in my case and then hold down the shift key and click on within my cases sheet 5 so I'm just gonna leave the blank sheet 1 there I can just right click on any of the sheets I've selected and choose delete choose that I definitely want to do that and that leaves me with just our blank sheet 1 what I'm then going to do is head back to the VB editor and I'll just maximize the the window this time so what I'm going to do next is just make a copy-paste of this subroutine so I'm going to copy the entire thing and paste it in immediately below I'm just going to modify the name of the sub tune so that we don't have two routines with the same name in the same scope I'll call this import wise our courses with loop and the idea behind this one is going to be that I'm going to import all the wise owl course tables but I'm going to have one on a separate sheet now ordinarily in VBA when you want to process a set of items in a collection you use a for each loop but suddenly the query tables object doesn't expose the web tables as a collection it's just a comma separated list of values so a more appropriate loop to use in this case is a for next loop and for a for next loop you need to have some kind of counter variable so I'm going to do is declare a variable called I as integer and then a little bit further down once I've set the URLs I'm always going to get my data from the same web page the same courses page from the wise owl website before I start creating new worksheets I'm going to begin my for next loop so I'm gonna say for I equals in this case I'm going to set the range that the loop will work over to the number of tables on that page so I know that it starts at 1 and it goes up to 3 so say for I equals 1 to 3 that point I'm just going to select and indent all the remaining lines below and then after that just before n sub I'm going to say next I so that forms a closed as a loop at the point at which it will return to the top and then start processing these details game so for each one of these numbers 1 2 & 3 we're going to create a new worksheet create a new query table on that new sheet pointing to that page with the same destination each time and then set up the properties of the query table the really important thing we're going to do here is rather than specifying an exact comma-separated list of table numbers we're going to reference the value of our I variable so appreciate that this is an integer but it will be implicitly converted into a string so that it will query the correct item so at that point let's just restore down the VB editor window and then we check in to execute this by pressing the f5 key and we should see that we end up with 3 new sheets it might take a little while for each one of those to refresh but we've got sheet 19 here we've got the geeky nerdy courses on it then G 18 and then sheet 17 as well each one with a different query table showing me a different range of values okay so we've dealt with an example where we've got a fairly static basic website like the white cell list of courses which is great as long as you just want I guess a warm-up import if you've got something a bit more changeable like for example our currency exchange site then you need something that's a little bit more dynamic and flexible to handle that so what we going to do is develop a system that creates a single worksheet with a table of the these top 10 conversion rates but we're going to make this worksheets sort of queryable so you'll have a couple of parameters that you modify to change the table of data the you return to get started what I'm going to do is head back to the VB editor and the Excel workbook and I'm going to let's see I'm going to head back up to the very first subroutine that we wrote in fact I'm going to maximize the VB editor window so I've got a bit more space I'm going to copy the entire subroutine called import wise el Corsa so I'm just going to copy that to the clipboard and then insert a new module so to start with I'm going if I paste that simply seen in and just modify his name to start with what I want to be able to do is establish which table from my website I need to import I'm going to call this one import X rates I'll try that again and then we're going to modify this subroutine just to establish which table we're going to be working with okay so first things first I'll need to modify the URL of course so let's head back to the web browser and we can copy the URL from the address bar and then simply head back to the VB editor and paste that in in place of the Y Zell URL I'll add a new worksheet for the time being and the connection is all set up fine we'll stick it into cell a1 for the time being what I'm then gonna do is just modify the name of the table I'm gonna great so I'm gonna call this one X rates the formatting doesn't really matter in this case what I do want to do here is rather than getting specified tables I'm not sure because I haven't looked at the HTML of this page I don't know how many tables there actually are on the page or which ones I should import so what I'm going to do just to give myself a bit of a clue about how to modify this and make it more specific I'm going to go back to excel all tables and then I'm just going to temporarily comment out the web tables line having done that what I'm going to do is I'm just restored down the VBL to window make sure I can see Excel in the background and then I can execute this subroutine just to see what I actually get so I appear to have got a couple of different tables back from this particular query so I've got one table here and then there's a break there's a blank line and another table if I just scroll down in the Excel window so it looks like there are just two tables in that page so I could choose to go with both I could tap import them both what I think I'm going to do here is just limit myself to just one single table so having a subset that there are in these two separate tables I'm going to go back to the VP editor and I'm going to modify this so it's not Excel all tables anymore I'm going to use excel specify tables and then I'm going to uncomment the web tables property and make sure that I'm only guessing the table number one so again just to give that one a quick test if I execute that subroutine again this time I only get one single table okay now that I've establish I'm getting the right results what I'm just going to do a little bit of tidying up in the worksheet so I'm going to take away all of the extra worksheets apart from the original blank sheet one and if I delete all of those I'm going to make a last little tweak to my import expert Peck rights routine I'm going to make sure that I put the data are starting at cell a5 and having done all of that and you're going to run this one one more time and then I've got the basis of a simple query Abul model so next job is to create some way for the user to choose a currency so rather than just getting US dollars all the time we'll give them more option of different currencies and also actual amount to convert now the reason we're going to be able to make this system is because of the way the URL works so this particular website if I switch back to my web browser I just have a quick look at the URL for the xr8 comm site you can see that after the the page that I'm browsing - there's a question mark followed by a series of parameters this is referred to as a query string so after the question mark it in case what parameters have been passed into this page to give me the right results since you've got a from parameter here that's currently set to USD I guess US dollars of course and then amount equals one so it's a reasonably good guess that there's not I mean not always this off yes of course certain websites make these a little bit more obscure but in this case it's fairly easy to guess what these how these parameters work we can modify them either by using the user interface has been provided to us if I choose GBP for British pounds and that will update the tables and you'll see that the query string has changed as well another option there was euros I could actually happily modify this manually in the URL so I delete GBP and type in e you are for euros and then just hit enter we'll see this time that it changes to euros I can also change the amount and I can do that again quite happily either by typing into the user interface or just manually editing the query strings let's say $1.99 and see what that gives us so you see all these values change the results at the webpage now because this is essentially just a string it means we've got the opportunity to build this URL out of various component parts so let's head back to the workbook let's create a couple of simple inputs for the user to select both the currency and an amount now I'm not going to spend a huge amount of time making this look attractive so what I'm going to do I'll just zoom in a little bit and in cell a1 I'm going to type in a quick little title here that says currency and then exported out what I'd like to do is create a drop-down list to restrict what values the user can select we could use some fancy form control tools at this point what I'm going to do just for convenience is use the data validation tools in Excel so I head to the data tab in the ribbon and choose data validation or I get them is a dialog box allowing me to specify or limit the values that can be entered into this I'm going to choose from the drop-down list the option called list and there's a couple of ways I can populate my list I could have a range of values typed into a worksheet somewhere and I can use the range selector to select those cells alternatively I can just type in a comma separated list into this box directly so let's just do a couple of basic ones will say USD followed by a comma then GBP comma and EU are and if there were any more that I wanted to use I could work out what those value should be from the query string just go back to the website and do a few tests and at that point I'm just going to click OK and I'll see that I get a little drop-down list I can select where are those three values so I'm going to choose GBP for the time being we can use a similar sort of technique to allow the user to select an amount so let's set it to cell a2 and type in a quick title we'll call that one amount and then in cell b2 what I'd like to do is restrict the user to only entering some kind of number so I'm going to select cell b2 and then head back to the data tab with them in the ribbon and choose data validation now this time I don't want to allow any value I would like to allow a decimal number and I can choose to assign a value between a minimum and a maximum or I could have a variety of other comparison operators I'm going to choose between actually between seems like a sensible choice and I'm going to have a minimum here of let's say 0.01 and then a maximum of a let's say 100 I think that seems like a fairly reasonable sensible range it's possible to assign error messages if you've dealt with data validation before you'll be familiar with this little technique but I'm just gonna head to the error alert page and make sure that I'm stopped if I try to enter the value this is a decimal number between the range I've specified and maybe add a quick little title here that says invalid excuse me invalid value and then or invalid amount perhaps might be a bit of a better message this is just to avoid seeing the generic you entered an invalid value message you can say the amount must be a decimal decimal value between I can spell properly 0.1 and 100 so that point if I click OK while now find is either type in a decimal number between the range I've specified everything is perfectly fine if I try to type in text or anything that isn't about it number I'll get the message that I've typed in so it's kind of like a convenient way to display a message box but without having to write in any VBA code now data validation of unit again if you know this apologies for mentioning this already but if you know about data validation then you'll know there's one surefire really easy way to override the validation if I type in any random bit of text I can happily just copy that cell and then paste it over the top of the validated cell and that will essentially destroy any validation that I've added I'm just going to undo that just to go back so just bear in mind that this and this is not a 100% robust way to do this if we had more time available if I wanted focus on it would use some worksheet events perhaps or maybe even some fancy ActiveX controls or formal controls to create drop-down lists and input boxes but for the time being I think I'm kind of happy with that system as it is the most important part of all this is creating the update system so what I'd like to be able to do is have a button that I can click on somewhere here that will feed Ian the values that I've selected from cell b1 and b2 to generate the query string of the URL so let's head back to the VB editor and I'm just going to maximize the window at this point and at this stage I'm just going to make another copy of my import ex rates routine so I copy all of that and paste it in just below I'm then going to change the name of the subroutine so it's called update ex rates and then let's have a quick look at how we can modify our URL first of all it'll help us a little bit to have a unique name for our worksheets so the work we've created with our little system on it is currently called sheet 7 so it's got both a code name and a sheet name if I still let to that object in the object Explorer and then head to the name property at the bottom I'm going to call this one ws rates then I'm going to move this little line here set WS equals work she start add I don't really need any more certainly don't need to add a new worksheet all I need to do in order to reference the data table that I'm on is is referred to as WS rates so let's just remove that for the time being what I'm then going to do is start modifying my query string so I've got everything up to the from parameter from equals I'm going to type in a closed-up double quote at that point and then after that I'm going to type in an ampersand and replace the USD with a reference to range be one on my WS rates worksheet so I removed the USD and then instead say WS rates dots range b1 dot value so that refers to and reads out the value of rage be 1 on the WS rate sheet of course it does at that point where any to do is have another ampersand to concatenate the next path the query string and the next part of the query string was ampersand amount equals so I just opened up some more double quotes here and then I've got ampersand amount equals after the equals I need to close it up notice in fact I can get away with just deleting the number one there because what I need to do do then is concatenate the value of range b2 so I add an ampersand at the end and I can simply then copy and paste what I typed in earlier WS rates print b1w and update that to range B to value let me just change the way this looks just so you can read it all on one single screen with now break it down across multiple different lines using the continuation characters so I'll just space and underscores and hopefully that makes it a little bit easier to read okay so just a few more changes still to make I don't need to write a new query table to my query tables collection oh and I also need to change the worksheet that I'm referencing here as well as I've removed my WS variable epoch but I haven't removed it at all so let's just delete that one as well but I don't need to refer to WS anymore I'm not generating a new worksheet I've got a specific named sheet called WS rates so let's change that to set Q T equals WS rates query tables and then rather than adding a new one what I'm going to do is replace all of that with a reference to the first query table on that worksheet now it's all query tables or index I've got a number and indicating in that position so I could just say query tables 1 I know that I've only got one query table but as I've given my query table a central name from earlier on it might make a little bit more sense to use that name instead so rather than saying 1 let's use X rates just to make the code a little bit more readable so let me just get rid of a few blank lines not much more to do at this point I can get rid of basically all of these properties that are modified earlier on I don't need to change any of those at this stage so let's get rid of all the extra properties apart from the refresh method we'll leave that in there the only property that I do need to change is the connection property and this is ordinarily what you set when you add a new query table but it's a property that can be modified afterwards as well so I'm going to set connection to be equal to and if you remember that all URLs need to or all URL connections for query tables need to begin with URL semicolon close double quotes and then concatenate to that the URL that we've just constructed so at that point we've got all the code pretty much written so let's head back into the Excel workbook and create a nice useful sensible way for the users to run this code so let's head back to the Developer tab in the ribbon and find the insert tool about halfway along I'm going to use a basic form controls button for this so in the top left hand corner of the insert tool box I can click on the button then either click and drag or click once on the background of the worksheet I'm going to choose to assign my update X rates so put it into that and then click OK change the buttons name so whatever it happens to be let's call it update instead and then click away from the button just to activate it should you need to make any further changes the simplest way to do that is to right click on the button to select it and then you can just select edit the text again so rather than clicking on it with the left mouse button because what that will do hopefully fingers crossed is run our code so we're currently looking at one u.s. dollar I've set it to GBP 0.5 so if I click update what should happen it's a query table will refresh to use GBP so let's give that a couple more tests let's try euros and say I know $1.99 and then click update again and again after a short pause the query table will update so there's a nice kind of basic interactive little system the end user to update your query tables now I have talked a little bit about using basic data validation techniques to control what the user enters here do bear in mind as I mentioned before that data validation is not necessarily the most robust way to do this so that I've got a drop-down list of values I could happily clear the contents of that drop-down this just by hitting delete and then if I try to update it to a personal to the the query string which would causes it sense well it would cause the the connection not to update if I click the Update button in fact you'll see it's trying to run the background background query down at the bottom but it's never going to come up with the solutions and just going to click here to cancel and then choose to stop the refresh now I can close down the the dialog box so if I wanted to defend that from happening I could write some code to check that that cell b1 was populated and likewise we saw that we could easily override the basic data validation if I do type in text the validation should be triggered and tell me that I can't do that but if I copied and pasted it over the top of that cell then the validation will be broken so we could write a bit code to just handle those two things just to wrap up this video make it a little bit more robust so let's head back to the VP editor and we'll add a tiny little bit more code to the top of our update X rates routine the first fairly easy thing to do is test whether cell b1 contains a value so we've done this in several videos previously what all I'm going to do here below my variable declarations is I'm going to say if WS rates dot range be warm don't value he calls an empty string then depending on whether I'd like to display an error message or not I could just simply say here exit sub in a single inline if like so then if I did want to display an error message then I would have to have some kind of block if and have a separate message box statement sort of a message box that says you must choose a currency for instance like one you must choose a currency and then you could have I guess a VB warning symbol on there so Phoebe exclamation I'm sorry there we go then we'll exit the syrup and we can say and testing if we've entered a value in cell b2 is beli similar we could also test if the value in cell b2 is a number or not using the is numeric function so in fact if we just go straight away we're saying if not it's numeric then open some parentheses and refer to range b2 valid I'll just do a quick cheat copy and paste WS rates range b1 dot value and then sorry I've got to copy it let's do that again and then I change the cell reference cell b2 instead so if not is numeric and then you can have maybe a message box saying the amount must be a number and then we could also have a VP exclamation importantly we should exit the sub so the rest of the procedure doesn't continue and we could then put in an end if if it we'd established that it was a number that we'd entered into the cell we could also have we could have melts close I guess here so we could say else so if it was a number we could check if that in the Mose within a certain range so we could do that within an else block of this main block if or we could also just wait until we'd reached and do this in a separate if statement so we can only reach this next if statement we can only get past this end if if the value in that cell is a number so we could just do this in a completely separate if statement so let's say if WS relates dot range be to dot value and we want to say something like if it was less than 0.1 or WS rates dollar range BT dot value was greater than 100 then message box or you could enter a message specifically telling the user what range is valid let's just say that amount is too low or high to avoid having to write out a really long complicated message I suspect you're getting bored of what just watching me fumble around trying to type properly at this point so exit soap and then end if that's a few basic techniques you could use just to make sure that the user typed in sensible values let's give that one all the quick test like by heading back to the workbook and let's try filling in or making a few mistakes make sure the validation works first thing I guess we should do is clear the contents of cell b1 so let's select that and hit the Delete key on the keyboard and click update and we must choose a currency oh yeah of course you must select do that let's go for us or let's go for euros instead let's then try if I type in a bit of text in a cell somewhere and copy and paste that over the top of cell b2 just to break the validation what I can then do is check if I click the Update button the amount must be number oh yeah of course it must so let's try it setting in a negative number and then click updates it's a number but it's too low or too high let's try it having a higher number let's go for a positive number that's too high as well let's pick somewhere in the middle let's say 50 and then click update and finally what I should see is it having established i i've validate all my rules or passed all my publications x i get a sensible update whew so there we go um I'm sure you can expand upon that idea and come up with some much fancier things and I've done in this video but those are the basic core principles behind creating query tables connected to web pages I hope you found that one useful and enjoyed it 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 leaving some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 74,111

Rating: 4.9331307 out of 5

Keywords: excel vba, visual basic for applications, querytables, query table, web query, web page, vba, wise owl, currency exchange rate, url, query string, url parameters

Id: ZrPEAljQjRM

Length: 47min 6sec (2826 seconds)

Published: Fri Nov 11 2016