Into an excel workbook the technique he's gone for is to use keyboard shortcuts to select all the data copy it and then paste it into a worksheet which isn't necessarily the.
Best way to achieve this but it does provide a nice starting point for this video so we'll begin by looking at how to get the send keys method to work then i'll give you two further options and.
Then you can see which one you like the best so let's get started so here's the data in question displayed in a chrome browser window and you can see from the address bar the url of the.
File shows its csv file hosted on github user content all i've done in excel terms so far is created a new workbook saved it as a macro enabled file and then in the.
Visual basic editor created a new module with a subroutine that fires up a new instance of chrome and navigates to that url of course all this is dependent on.
Of videos which explain that so if you need a bit of a refresher or a bit of help getting that done then this is the playlist to look at just to demonstrate that the basic code.
I've written so far works if i just run that subroutine we ought to end up with a new instance of chrome navigating to that csv file on github user content now let's add the code that lets us send.
Key presses to the chrome browser if we head back to the visual basic editor we'll start by declaring a variable that can hold an instance of the selenium keys class so i'm going to say dim ks as.
Selenium dot keys we'll then need to create a new instance of the keys class and there's a couple of different ways of doing that i'm going to do it explicitly by saying set.
Ks equals new selenium dot keys once i've done that i can send keystrokes to the chrome browser by saying cd dot send keys if i just think about what i would do to.
Select all the data and copy it to the
Clipboard the two keyboard shortcuts i'd use would be control a followed by control c so the first parameter of the send keys.Method lets you specify either a key or a modifier key so i'm going to specify the modifier key control so i'll say ks dot control followed by a comma and then the key.
That i would like to press in combination with the control key which will be the letter a to select all and i want to duplicate that in fact i'm just going to copy and paste that line.
And then change the key that i'm going to press in combination with the ctrl key to the letter c even without pasting the data into a worksheet we should be able to test that.
Our keyboard shortcuts are working if we run the subroutine we'll open up a new instance of chrome and it should be fairly obvious that we've now selected all the data.
And if we've selected everything we should also have copied everything to the clipboard so let's just head back to the excel worksheet and if we just choose to paste something we should find.
That all the data was indeed copied to the clipboard and we've now pasted it into a worksheet a nice way to finish off this example then would be to automate the pacing of.
The data into a new worksheet and then tidy up the results by splitting the data into different columns and then maybe changing the column widths as well to get that to work let's head back to.
The visual basic editor and we'll declare a new variable to hold a reference to a worksheet so we'll say dim ws as worksheet we'll then create our new worksheet.
Object i'll say set ws equals this
Workbook dot worksheets dot add and then after we've copied all the data i'm going to use a width block to refer to range a1 on the worksheet we've.Created so with ws.range a1 i'll close off my with block with an end with statement and then inside there i'm going to say.
Dot paste special and then i'm going to refer to the current region property and let's apply the text to columns method to that i'm going to set the comma parameter of.
That method to be equal to true and then finally i'm going to refer to the current region the entire column property and then the autofit method.
Having done all that we can run the subroutine again and we ought to end up with a new copy of chrome opened up all the data copied from that csv file and then.
Back in excel a brand new worksheet with all of the data in there for the next example we're going to access the text property of the element which contains the csv data so this is a.
Lot more like the way you would scrape data from a web page if we head back to the chrome browser that has the csv file open in it i can right click on any part of the csv data and choose to inspect it.
And in the elements list i'll find that all of that data is contained in a pre element a pre-formatted text element if i expand that there's all the data so i can just treat this like any.
Regular web page looking for elements or tags with the pre name so if i head back to the visual basic editor i think i'd like to keep the.
Original version of this subroutine so i'm going to make a copy of it and paste it in just at the top of the module i'm then going to change the name of it of course so let's call this one write.
To text file or something along those lines and there's a whole bunch of stuff we can get rid of here so we're not going to need to have a new worksheet or a.
Keys variable so let's get rid of those i'll get rid of the worksheet um set worksheet variable as well get rid of the lines which send keys and all the the width block which ties.
Up tidies up the data and then we're going to have another variable at the top which allows us to hold a reference to a selenium web element so let's say dim.
E as selenium.web element then once i've loaded the url i can say set e equals cd dot find element by pick your favorite element finding.
Strategy i'm going to go with by css and then in some round brackets and double quotes write in the word pre close the double quotes in the round brackets.
And then just for demonstration purposes i won't be able to see everything here i'm just going to say debug.print e dot text so it will help if i can actually see.
The immediate window for the debug.print statement to work if i head to the view menu at the top of the screen and choose immediate window so i'll run the subroutine again.
And when we load the page and have a look in the immediate window once it's finished we can see all of that csv data being printed out not quite all of it the immediate window does have a limit.
To how much it can display but you can see it is getting data next we can write all this text into a new text file and to do this i'm going to use the microsoft scripting runtime.
Object library i'm not going to go into a lot of detail about how this works simply because we've already covered this in previous videos in the excel vba introduction.
Playlist so the files and folders and text files videos are your reference points if you need a bit of a reminder of how this works to start with in the visual basic editor.
I'm going to go to the tools menu and choose references and then i'm going to scroll through the list to find the microsoft scripting runtime i always go past it the first.
Time but there it is so make sure you place a check in the box once you've done that and you've clicked ok we can declare a couple of new variables so just above the selenium web.
Element variable i'm going to declare one called fso as a scripting dot file system object and then below that dim ts as scripting dot text stream.
What i can then do once i've captured a reference to that element i'm going to create a new instance of the file system object class so i'm going to say set fso.
Equals i'll try to say equals new scripting dot file system object and then create a new text file or rather open a text file.
I'm going to say set ts equals fso dot open text file i'm going to provide a file name which doesn't currently exist but one of the nice things about the open text file.
Method is it allows you to create the file if it doesn't already exist the file name i'd like to place this in the same folder as the workbook i'm currently working on so i'm going to say.
This workbook dot path and then concatenate to the end of that a backslash and then the name of the file i'll call it what do we call it let's call it covid which is what the.