Contoling Chrome from VBA with Selenium

all right so let's start off by installing selenium it turns out it's not too bad but there is one kind of confusing thing about it so what is today today is December 2nd ok so I've given you this a link here in the schedule for notes on installing selenium hopefully it will open see I am connected found an old blog that I started by about 10 years ago and I think a TA actually made some posts in it but I haven't done anything for a long time so here's where I wrote the instructions so they be kind of easily available so are we all ready to install so step one is follow this link and we're going to download the most recent version of selenium basic so's selenium basic is a project or if someone said you know what there's selenium out there but it doesn't natively work with VBA we said I'm gonna write a wrapper for it something that will expose selenium because slean's open-source we could do this expose what selenium does to the component object model which is what we need to be able to interact with it in VBA so we're just gonna follow this selenium basic link right here and that should give you a file to download it is a Windows installer and Matthew maybe Rome and help people follow the instructions if there did I click on that look oh oh no I'm sorry that's where I am sleeping basic that's where we are but we have to get this Slee and basic got exe this is the Installer go ahead and save that ah and you're gonna you're gonna open that and just run it accept all the defaults but don't don't go too far because you have to pay attention you have to you have to notice where this is getting installed because we're gonna have to do something with where it gets installed so let's just see if there's anyone who's still trying to get that file down I want to get to this point to where we're all at the Installer kind of ready to go anyone saying I need another minute or need some help that went great here and then just a bit and there's the top one Rebecca let me come take a look you squeeze past you here oh really okay yeah once you wouldn't try that okay good anyone else okay so will accept the you know go ahead and do next accept the license but we're not going to actually do the part that says installed just yet so next I'll accept the license next all these defaults are fine next right here you need to remember this path right here we've got to change one of the files that's here so we'll see how to do that but maybe just copy that put it in notepad or even just ultimately we're gonna have to go there so just open up Windows Explorer and go to that location and we'll we will use this actually me not it may not be there yet for you so probably just keep track of it by going to notepad and paste it in so we will come to that later and then just go ahead and hit install and it doesn't take that long to install but it should get everything you know less than a minute we should be installed actually I did not need to install cuz already installed on mine uninstalling yes I had an install first now it's installing I am NOT going to install the ide add-on for firefox you can get that directly from firefox there's one for chrome as well we won't be using it here in class I don't think the one that's trying to install here will work anyway and just go ahead and click finish now my smoke I took inordinately long is there anyone else still waiting for theirs to go nope okay so now let's go ahead and open up this location that we copied and this file right here the chrome driver you'll see there's a chrome driver there's an edge driver there's a Firefox driver there's an IE driver there's an opera driver these are the main those are the only those are the only browsers that I know that selenium will control you have to have a driver that's matched up to the browser that you're going to try to control and as it turns out this chrome driver is outdated so this project selenium basic was mating like 2016 and it hasn't been updated since then chrome has kind of come along and so this driver that's with this project is now out of date you can't use this driver with your current versions of Chrome so next step we'll leave that open there for now and then our next step back here in the instructions is to download the current chrome driver so we've got selenium and then we've got the driver for the for the browser that we're going to use this question right here so I didn't quite follow all that did it work let me just go ahead and get that for the recording if someone has the same problem oh okay yes so you saved it and you had to copy and paste it you couldn't just navigate through to it so there's something hidden in there that's not letting you see it yeah so yeah hopefully you copied it and then pasted that did that work or did you not copy it okay okay so next step two is to download the Chrome web driver so here's another link we'll just follow that link and we're looking right here for the latest release so I'll download latest release Oh actually that's going to take me to the different platforms that it's written for so it could be on Linux Mac or Windows I'll choose the Windows one and this will just download a zip file I'm just gonna save that file and then open it I do not need to extract it so I'll just open it so I can see it here it's still in the zipped folder but here's chromedriver Exe ultimately I'm gonna want that one to replace the existing chrome driver that is in my selenium basic folder so let's just make sure that we've that we've that we've gotten there I want to see side by side the two folders 100 to stuff into my Christmas dinner folder so one is that chrome driver that we just download it's a zip file I've got it open I didn't if you extract it's okay but you don't need to extract it so I've got it here and then over here I have my selenium basic folder which is the one that we navigated to it work you got it okay no no if it doesn't say that Exe just means you're not showing the it's not you're not showing the names but let's let's do take a minute and show that to you it turns out that the folks who make decisions about Windows but you know those pesky file extensions are too confusing for my mom and they're right and so they said by default we're not going to show file extensions now you are college students your business students well you're not all business students but you shouldn't be afraid of a file extension um and and there's some things if you really need to know the file extension so let's just see how can we show those again I'm just gonna choose view up here though on the the ribbon menu and then I'm going to choose options over here and if you're not already showing your file extensions I encourage you to do this you could probably get by without doing it but let's in your life you should see file extensions I'm gonna click on options and then in the little thing that comes up I'm gonna choose the View tab I'm going to uncheck where it says hide extensions for known file types just uncheck that you're gonna show all extensions whether you know them or not just show all extensions then say okay what's that your question you know questions can we walk through that again sure so I'm going to come to the I'm here at Windows Explorer I'm clicking on the View tab and then over at the right there's options I'll choose options here that will bring up dialog box with another view tab like that view tab and then down about 10 or so options down there's one called hide extensions for known file types just uncheck that one and say ok all right so now over here on my right hand side I have the new chrome driver the updated chrome driver and then over here in my slinging basic files I've got the old outdated chrome driver I'm just gonna delete that one right click it and say delete and then I will drag my other one right over and I will just drop it not not on a folder and not on one of the files but just over here kind of kind of scrolled for that I wanted to scroll go back just over here somewhere so that my little label says copy to selenium basic if I if I drop this on one of the executable files like here it will actually try to run that executable file and and pass it this file as an argument don't want that so just wanna make sure that we're copying it there we go copy to selenium basic and that will copy that file in place so now I've got my chrome driver back here but it's the up-to-date one eventually chrome is gonna update again for you and the next time it updates it might not need a new chrome driver but at some point it's gonna it's going to go past what this Chrome driver can do and you'll have to do this again your code will stop working I've got a new chrome driver to work with Shalini it's just part of the way this ecosystem works okay once we've done that that should be all the installation that it takes there anyone who had any trouble getting there that's great so let's see what it takes to now automate chrome from VBA we have a file to download today from learning seats I'm gonna go back to learning suite and like there's two I'm gonna go ahead and download truck list dot xlsx I won't put it in Christmas dinner I'll put it in downloads we do have to add something to the references to use it but we got actually have Excel open for that first book but-but-but-but-but selenium basic is installed we're ready to go and I'm also going to download parser dot CLS we'll get to that a little bit later today then I will open Kalista xlsx and this is the one that we're going to work with factor I will I'm going to go ahead and save it straight away as an XLS M so I don't forget to do that because we will be writing code into it I'll save as truck list is great I'm gonna call it XLS M and call it fall of 19 okay so here's what we have is we have a bunch of compasses to task for today we got a bunch of company names here and we need to get the phone number and the fax number fortunately that information is maintained by the United States Department of Transportation you know these are commercial carriers if you ever been driving down the freeway and you pass a semi-truck and it says US dot number it has a number on the side of the truck have you ever noticed that you've noticed that that so that's what that's what these that's what these numbers are so you know if you're a commercial carrier you've got to be registered with the Department transportation and that number gets you there one day my wife and I were driving across Iowa yeah actually I didn't say Nebraska we had just driven across Nebraska that's the one to be sorry about it's a long state we're into Iowa and you know we're we're not you know going terribly fast maybe 65 70 miles an hour there's a truck going a little bit less fast than us in front and we tried to pass it well come to find out there was a truck in front of it that was going even slower and so this truck I'm decided that it was going to pass a truck in front of two lanes at the same time that we were right next to it and so it starts to merge over and it sees us and kind of swerves back in you know it's kind of like whoo that was close and so you know we you know as soon as that happened we hit the brakes and dropped back but you know he came over and I said okay I guess is gonna wait for us and then we you know started to pass it he pulled over to block us but didn't pass came back to his lane and we tried to go again and he did the same thing he wouldn't let us pass he had on the back of his truck a u.s. department transportation number was there we could see it and so this is before the days that you had internet on your phone but at a phone so I called my sister will you please go to this website that we're gonna work with here and look up this department transportation number and give me a telephone number of the you know that the red that the registered party we're driving through Iowa by some miracle we had cell reception and I called you know the register I said listen I'm I'm here in Iowa and we've got a what are your trucks is you know driving erratically you want the number he said no I know we only have one truck in Iowa right now all uh you know San by just hold I'll get on the radio with him we held for about almost a minute and got back and said mr. Allen I think I told him I was mr. Allen the driver is going to take the next exit and then you'll be you know be safe to pass and then he dead next exit truck took off I passed I just couldn't imagine you know what that driver was thinking that his boss calls him let the guy behind you pass ah all right so but we need the phone number so that's the test you get the phone number for all these registers okay so that's gonna be our task today and I think we're ready to ready to start working with selenium so alt F 11 I'm going to come in here and I am going to insert a new module insert module it's like my immediate window is huge all shrink that down okay now right so the students said hey don't we need to add something to our references and the answer is yes so we've done this on one maybe two other occasions most of times when we work with objects in VBA so far we've worked with objects that are just kind of there and ready to use but there are some objects like working with a database connection that just they're just not everyday things that we always have available in Excel and so we need to tell the VBA interpreter these objects are and that's what the reference does so now that I've got my new module created I'm gonna choose insert oh no I'm sorry I'm gonna choose tools references and I've got to scroll down and find the selenium type library and check the little box next to selenium type library I then say okay that adds the reference to this project that says hey interpreter you know for all the stuff we're gonna be using you know be aware of of the selenium objects so maybe I'll put a comment here be sure to add a reference to the selenium type library okay now I think also over on the how to work with Chrome I've given you a little block of code that might not be bad to start with so step four make a module step five we're adding in this code to be a pretty good starting place for us the first thing that will be nice is that we can now just run this and see if it works if this works will know that we have everything set up correctly this should just open up a Google advanced search plug-in some data and make a search so here it has opened up Chrome and it did it went kind of fast you might have seen it but it did this colonial Heritage Festival search and here's there's a search so we successfully controlled chrome from VBA open the web page drop some text into it and then told it to submit and so we want to make sure that that has worked for everybody because if that doesn't work we can't go on oh no I'm seeing more hands than I want to see okay I better pause the recording if I can click here to pause alright so maybe just shy of half of the students here once that same process and they weren't able to get it to run so hopefully we can track that down I can post another video but for now if you've got it going great come along otherwise just kind of watch and see how it goes okay so we were declaring an object as a web driver so this is this the selenium object that allows us to control a driver we could actually use the keyword new dim bought as new web driver I did it here in two lines but we've worked with objects like that before so now I've called it bot and I'm telling it to start up using the chrome driver so go ahead and start Chrome so that we can control it I would use Firefox or edge here if that's the one I was doing okay and then this is how I tell it to get a page so in this case I'm going to go ahead and come over to the page and I for I gave you and a link that has a data that we're looking for so I'm gonna go ahead and put that URL in as the URL that we're going to and now I'll put a stop in here so that I'll run to that point and then we can kind of snoop around and see what there is to see tells me chrome is being automated controlled by automation software great okay so it looks like it has pulled up this page from United States Department of Transportation now I have here I've got the US Department transportation number and this page is gonna let me search by that number so I have to find out what this I got to find out how to get ahold of this object so I'm gonna right-click it and choose inspect now this ends up being a little bit too small for me to work with here so when this inspect window comes up I'm gonna choose a little more menu not the main one for Chrome but just for the chrome developer tools and I've got you know like four different options where do I want it to dock I don't want it to dock so this will be a separate window it'll just give me a little more room to work with here so I'm now going back using alt tab going back and forth between the Chrome web page and the chrome developer tools which are showing me what's inside this one I'm not sure why it is but the first time when I say right click and say inspect it just opens up the tool but doesn't actually go to that particular location so I'm gonna right click and say inspect again and now it should actually get me down to that particular spot there's several ways one of the things that's neat about selenium is that there are lots of different ways that you can find an object on the page the best one always is is there an ID if it has an ID that's gonna be unique and you can just go straight to that one and does this one have an ID property no ID next best is gonna be name just have a name property the answer is yes there's a name property now there can be more than one object with the same name on the page and so we have to kind of be a little bit careful about that but we can see the name here is do t so I'm going to come back to my code and that's actually I think how I how I did the other example it founded element by name so I'm gonna take off the advanced search query name and put in do T it's gonna be case sensitive and now I want to send keys so send keys is what pushes data into or gives keystrokes to that object that object and does whatever it would do with a keystroke so you know for a text box it's just gonna accept those characters in you know other kinds of objects like if you're have something has the focus hit the spacebar it does something different you know you can like activate buttons and so forth hmm so now what I'm gonna need here is I'm gonna have to get my why don't we go ahead and just start this off working on Row two we're going to pull the data off of Row two to get that u.s. number so the question I send keys instead of just changing the value and the answer is we the answer is I'm not sure if we can't just change the value we could give that a shot but this is not the normal kind of send keys that you might find in VBA if you come across it what sendkeys does in VBA is it sends keystrokes to the keyboard buffer it just says hey pretend these keystrokes came from the from the keyboard and wherever keystrokes from the keyboard would go that's what they would go and so you would feel a little bit nervous with send keys because lots of things can happen that changed the focus as to where those keystrokes would go but in this case we are sending these keystrokes directly to a particular object they're not going out of the keyboard buffer they're going to an object and so this is the normal way that we put values into things in selenium yeah as I said I'm not sure if we can't even change the value of we might be able to but every example I've seen this is the way it does it okay so I'm just gonna do let's see our sheet is sheet one HGT one dot cells will put row number two for now usually before put number two why don't we come up here at the top and let's just set this up let's dim row as an integer because we're gonna have to generalize this eventually and then row equals two so now sheet1 cells row number row comma column number five I was right okay now the problem is I haven't run this code up here yet so maybe I'll just go ahead and stop this and run it again let's go ahead and put dot value on this and I'll go ahead and run that again opening up Chrome it takes a second for it actually to start up the chrome driver and that's the delay that you're seeing there okay now we've gotten to that page now we have a similar similar thing when we say bot get it knows to wait to suspend the code here until the page has arrived so we don't have to worry about is the page there but what get will wait until the page is here but when we click on that button we're gonna have to wait for it but let's go ahead and now step one line at a time and one should have put the Department of Transportation number into the form so there it is so we put the value there now we're ready to find out how do we get to this search button I'll right-click it and I will say inspect and oh because I close the browser it separated that again so we'll undock it maximize it inspect again okay we want to see the second we want to see id is there an ID no ID next we want to see name is there a name there is oh there is name submit darn it we'll get a some more interesting ones later so we'll use name because if there's a name we'll take it okay there's a question over here ah okay so I think we're not too far along that we couldn't bring everyone else with us so we have a student who has figured it out you get a gold star for the day as it turns out gold stars don't affect your grade but they're good have tell me what do you what'd you figure out up get the dotnet framework alright you're gonna talk me through that again okay so for those of you who were getting the automation error let's give this a shot so I'm gonna go back into selenium basic yeah okay and now tell me the steps again I'm going into the scripts folder realize you might have closed that so let me give you a minute to get your selenium basic folder back open anyone who's struggling with this not there to this not not yet there's a slimming basic folder you'd wait we're good okay I'm going into the scripts folder start Chrome VBS okay so I just started that and it looks like mine worked okay let's see what okay so so it didn't but it gave you something to try nope it's start Chrome not VBS right there say okay hold on a second and then yeah download and install this feature yes so when that comes up it gives you an error message you can say download and install this feature then what happens and it works after that works close I'll start again and we should be in good shape excellent yeah once you get go to scripts start maybe I know start chrome yeah that's the one say okay click on download install and then just go with the flow going anyone else need help now one step just close Excel home open up Excel again start EBS start Chrome VBS you're still going we're still waiting all right so we are finding by name and it's submit its capital S on the submit yeah submit okay I'm gonna go ahead and run that and so I can find it okay so here it is looks like if submit it we've actually found the Duke brothers is that what we were expecting to find row - yeah the Duke brothers so we got the right data we're looking good I see a question over here no okay but now we have a situation where we have to be sure that we got to tell the page somehow to wait now many of the examples that you'll see if you start you're looking for googling selenium and such and you will see examples in lots of other languages besides VBA up there are a lot of folks in there saying you know what just pause for a second here that's a really bad idea because if you just pause for a second it'll be good enough 95% of the time maybe 99% of the time but if there's a delay for whatever reason then you don't really know so we want something that's a little more substantial than just kind of hoping that the page has gotten there so here's what we're gonna do we're gonna put ourselves into a little loop right here we'll put a do and a loop and we better put in two events in there and let us do until some condition now I'm gonna I'm gonna come back and look here one of the things that we could look at is we could look at the URL if the URL has changed we know we're at the new page and so is there something I could tell about the difference here and you are on I'm looking at this I'm seeing wow yeah that says search results here what did it say back here just says search so I what I could do is come in here and say you know what I'm gonna wait until the URL says search results that's one way that I can do it another way I could do it is I could wait and tell I'm looking for something about the page that's unique just kind of keep looking at the page that's here until there's one told what I'm looking for is there but let's go ahead and do this one with a URL so I'm gonna do until in string so I'm going to look for one string in seven that are starting at the first character looking at bot dot URL and I'm looking for search results capital search capital results now what does it returns a number from where the one string is found inside the other and it returns zero if it doesn't find it so I'm gonna do until in string is greater than zero so it finds something it'll never be less than zero zero means it didn't find it and then another number tells where it did find it now in sieve just do events which would put that into a loop and just go maybe do it thousands of times and Parshin of a portion in a portion of a second instead I'm just gonna tell it here to kind of wait I will tell it to wait a second so application dot wait and now what I give it here I don't give it how many seconds to wait I tell it to wait until a certain time which is really kind of annoying because I almost always want to wait for a duration and so what I'm gonna do is I'm gonna tell it one second from now there's several ways to do that but the way that's easiest for me to remember is to say date add there's a date add function I tell it what I want to add s means second I tell it how many one I tell it some date to add it onto so now will be the current time from the system clock so that says wait until one second from now no now plus one we'll wait for a day yeah yeah we could do we could do now plus take a day divide it by 24 divide it by 60 divided by 60 and and that would do it I don't want a way to date though that's for sure okay all right so now ahead and just run through this loop now it's already there so actually let's make sure that if you navigate it make sure you're already back here and it should just fly past that because it'll it'll meet the condition immediately yeah so it just goes right past it that's gonna that's going to pause and wait until the page has arrived okay good news is we've navigated the bad news is this page so doesn't have the data that we want we've could we've now converted a search for a for an ID a US dot number and now it's giving us the link that will have the data so we got to find this link and click on it there's several ways that we could do it number one if the links have an ID we can find that by ID and just click links almost never have an ID let's just take a look at it I'm gonna right-click choose inspect and there it is it's got H ref properties all it is is an H ref hypertext reference so one thing I could do is I could say just give me all the links on this page and then I could I can iterate through them and say you know let's find one that is there's a search details it's probably safe to go to the first link that says search details so if I had the whole set of links I could just navigate until I find that one and what's gonna kind do that as an example but that's not where I want to end up I'm not sure we have time to do it so another way that we can find a tag is to find a tag by a class and so this one a class is used for formatting and so different tags have different classes and so this one is the middle TD F MCSA so TD is table data this is just how they're coloring the different rows so you in the results they have a light band they have a dark band a light band a dark band this is one of those colors and so here's what I'm going to do I want to get a ref to this tag here I can get a reference to this tag and once I have that one I should be able to get one to the link pretty easily but what I'm gonna do here and although I could do this in kind of one long statement I'm gonna break it up primarily because if I break it up I'll get better help in the editor while I'm trying to figure this out so we've got a bot up here as a web driver I'm gonna dim hmmm I'm gonna I'll call it TD it's a table data tag that I'm after now I'll do it this way do it more general I'll call it LM elem short for element as a web element so here's another object from selenium so the bot is the whole browser the an element is just one tag so I can actually just you know bind on to its particular tag so here's what I'm gonna say now that I've got elem set there after my loop I'm gonna set Elam equal to bot dot find element by class by the way little note here in most other languages these methods find element by class our find underscore element underscore by underscore class and so if you're if you're searching for help and you you find an example you kind of copy that method and bring it in you know it doesn't work there something wrong with what we have here go ahead and check and see if you can find one that looks like it's the same here in your intellisense help so fine element by class inside parentheses in quotes I'm going to put that if I double-click it'll let me copy it and so I'm finding an element by that class that should give me the first element on the page that has that class by the way if I wanted to get the collection of all the elements that had that class instead of fine element by class I say find elements by class that will give me a collection of elements I'm just gonna get the first one I don't put that just goes the first one okay so I'm gonna drag my current state an indicator back to that and run it now I would just like to come to my immediate window and ask for the type name of elam and yeah it would be nothing if it didn't bind on to it but it did find on so it's a web element so now it knows what that is so now I can say e LEM dot and we've got several things we can do here including the same find element methods so I can look at this element say I want to find an element inside this element not on the whole page just inside this element so now LM dot find element by nee not by name a tag fine element by tag and I'm looking for the a at the anchor tag right that is my my next tag I've got this TD tag here and I want to find the first anchor that's here so this should give me a reference to this anchor tag the first anchor inside the first TD I found that had this class and so this will give me a reference to that tag and now I'm just going to click it I'm going to do another weight here and going to wait until every cooler it is so I guess I'll click it and then I'll just I'll just use f8 to execute this line so now we should have clicked it and search details so I can kind of use the same approach here because now it says search details instead of search results and loop here until we've found search details so finally now we've gotten to the page that has the data that we're after questions question is we don't have to include the brackets oh yeah yeah the question is like the angle brackets the greater than less than sign here and the answer is that's correct because I'm not parsing text Here I am I'm calling the find element method if I don't want my tag method of my other element it's looking for an element nested inside of it and so yeah if we were parsing text which we're about to do yeah those tags will be important because right now it's a whole structure of objects I'm not just a text file okay so now what I'm after is the two telephone numbers I got this telephone number and I got the fax number we only have 10 minutes to go and with a lot of work we could track these down using the same approaches we've seen so far with selenium we could dig in there we could find something that would allow us to reliably find that particular those particular tags and we could pull the properties out of them fact we're gonna want to pull the properties out okay but I'm gonna show you another set of tools that I think will be helpful to you and that's this other file that we downloaded it was called parser CLS so I'm gonna go ahead and stop my code and let's go ahead and import partial CLS CLS is a class file we've made two kinds of method two kinds of modules so far we choose insert we've made a regular old module and we've made user form which has a module in it - there's another one called the class module we're not going to create one of these that file that we that you download is is a class module that's been exported so it can be imported so I'm going to choose file import file and then I'm going to go find that parser dot CLS file which should be my downloads I thought it be here there it is pressure got CLS that has now given me a new group over here inside my project explorer called parser so this is now a new object I can create instances of one way that we tell it hey heard an object I want you to know about its go make a reference to it that's if it's a executable kind of compiled object this is a object to find it VBA we bring it in here and we may so you have the source couldn't come look at what all these are but these are just properties I made really for working with HTML text so let's go ahead and make a new object of this class so I'm gonna dim P as a new parser and I'm gonna run my code I'm gonna get rid of every stop except the last stop and that should get us to the same place okay so it's brought me back to here that's pretty good but now I've got a parser that I'm ready to use the methods that are available to this to be able to work with text so let's just explore this for a little bit I'm gonna come here and inspect this telephone here we'll float this thing out inspect it again yeah I'll go back to that in just second just go ahead and get dis inspected and then back to code so I did did was imported the parser class and then said Dimpy as a new parser and then I ran the rest of it as normal I did delete a stop that you might not have seen me do so that the only I stop I have is just before the end sub who's struggling take just a second did you paste that or tape it hey go go copy it yep class name right there you know just wrapped there yep oh and then and run from their lifecycle let's look at a page and see what brought him still didn't die let's take a look after class for now just go ahead and navigate there okay if your code isn't actually getting you to that location go ahead and navigate there by hand on the browser and we can keep going and then we can work and figure out why it's not working at the end of class okay so the parser just has methods that are helpful for working with text files in general and some things that are specific to HTML files so what I want to do is I want to put the text from this page into the parser here's what I'm going to say so I'm gonna say P which is my parser object dot text is equal to and now I want to get the whole text of the page here's how I do it so I've got my bot and I'm gonna get I'm sorry I'm gonna find element by tag and then I want the body so find element by tag the body tag which is the whole document and then to actually be able to get what's inside of the body I'm gonna say attribute and the attribute I want is the inner HTML so it's an attribute of that tag is the HTML that's inside the body tag by the way I could say inner text and that would give me the text just you know just the text with no tags I definitely want the tags here so inner HTML the parser kind of keeps track of where you are so the idea is you can kind of navigate through a file so it has a current position indicator so let me say the position is equal to 1 it's gonna be one to start off with but I like to set it equal to one so I can see I know I've gone to the the beginning of the file I'm just going to go ahead and run those two lines and make sure they work okay I should now be able to ask for P dot text and it should give me a whole bunch of HTML so that's that's the body of that page it's the last 200 lines of the body that page is all the immediate window holds all right so now I'm inspecting that telephone so here's the tag that says telephone in it the parser will just let me look through this as a whole set of text and so what I'm gonna do is I'm gonna navigate I want to try to find where it says telephone TD you might say Telep for all I know the neighbor's name some trucking company telephone brothers trucking I don't know so but I want to make sure that I've got this one that just you know maybe even is closing bracket telephone and then slash TD so I'm going to tell my browser or my parser to move to that location let me go ahead and print the position I'm at one right now but now I'm gonna say parser dot move to oops because I'm not doing anything with the result closing tag bracket telephone and then /tr if that is successful it will update the position and it was not successful so got something oh it's not tr it's TD go run that again now that is updated the position they say we're now twelve thousand eight hundred thirty-one characters in so it now has put that right to where I've moved to it's actually put me it's put it right after where I've moved to so the current position now in this file is looking right here it's right after this TD well if I do another move to it we'll move from that location to the next spot and so I want to find from there I want to go to where it says TD space there's a TD here that I want to skip by TD close bracket TD space will get me to the next place so I'll do another move too and that will move me to the next location TD space from there I to move to the closing tag bracket I'm not convinced this 98% is going to be that way all the time so I'm not going to rely on that and instead I'll move past it so we'll do another move - this time I'm moving now just to the closing bracket now I'm ready to get the text I've got to my insertion point now is sitting right after that bracket it's right before the telephone number I now want to get the text all the way to where it says nbsp that's a non-breaking space in HTML and I want to put that into sheet 1 dot cells row number row column number 6 that value and that is going to be P dot get text and we're gonna get the text from wherever the position is up until the next characters as I tell it to look for which in this case is an % n vs P semicolon so from the position it's going to say all right look until you find the nbsp and that marks the end of the text that I want and it will just bring everything between that which should just be the telephone number we run this we'll see it put it in and we'll call it good for the day so I'm gonna come back here I'm gonna drag this up back to where I have position it 1 so I'm setting back to the beginning of the file and I'll run and hopefully that is written the telephone number of the Duke brothers they're into the worksheet and so that just navigated that text file found the data and pulled it out folks that's what we've covered here in this video is the skills that you'll need to be able to do the final project for this class I say final projects not only a final project it's project number six so that should be it okay thanks for coming we'll spend one more day doing web stuff class dismissed

Channel: Gove Allen

Views: 30,028

Rating: 4.9130435 out of 5

Keywords:

Id: 2jZGhKugK70

Length: 56min 41sec (3401 seconds)

Published: Mon Dec 02 2019