Excel VBA Introduction Part 7 - Workbooks

welcome to this Wisel tutorial in this video we're going to cover basic techniques for working with workbooks in Excel VBA so what we'll cover in this fairly short session is all the basic techniques for working with workbooks we'll talked about how you can refer to workbooks and switch between the ones you have open on screen will then show you how you can open workbooks that are currently closed and also how you can create brand-new workbooks and finally we'll talk about the various methods for saving workbooks including using the save as method to choose a name and location and even how to change the file type so let's get started the first basic thing we'll cover in this video is how you can refer to and switch between different workbooks that you currently have open so you can see my project Explorer I have two workbooks open one called top movies 2012 budget where I'm going to be writing my coach ollie and I've also got one open called book 2 I'd quickly switch back into Excel you can see that I've got the top of movies 2012 workbook open and acting on-screen but I could switch to the book 2 workbook seems by clicking on this task what I'd really like to do is write some code that can switch between these two workbooks so back into the visual basic editor I've already started a subroutine and I'm sorry I'm going to start by showing you how to refer to workbooks by name so the first thing you need to type in on this line of code is the word workbooks you can either type that in longhand or of course you can press the ctrl key and the spacebar and look for the word workbooks in the intellisense list I press tab to type in the rest of the word and then I can need to open a set of round brackets and double quotes and then I need to refer to the name of the workbook that I want to activate so you can see the name of the workbook listed in brackets in the project Explorer so the workbook that I want to actuate is called book to dot xlsx the filename extension is important the name of the workbook is in its case sensitive but it is good practice to match case so I've made sure this capital B there although that isn't actually that important I can then close the double quotes and close around brackets and finally I need to say that I want to activate that workbook so I type in a full stop and look forward activating my intellisense list start typing it in or use occurs a key is to select and then hit enter I can do something very very similar to activate my top movies 2012 workbook again so I give myself blank line and then start the next line with workbooks open parentheses in double quotes top movies it is inkay sensitive but I want to make sure how much case just cuz I'm fussy like that dots xlsm closer to go some round brackets dot activate and there we go so to show you what happens when I step through this routine if I just drag my window down so that you can see the title bar at least of Excel if I then start to use the f8 key to step through my code you'll see that when I execute this line Excel switches to book - when I exclude this line they switch it back to top movies 2012 as well as referring to workbooks by name you can also refer to them by their index number so the workbooks collection is indexed in the order in which you open the workbooks what that means in my case is because I've opened the top movies 2012 workbook first that has an index number of one and then I created the book two workbooks that has an index number of two so if I wanted to replicate what this first subletting does using the index numbers I can simply refer to workbooks to dot activate and that refers to an active ace book - and then switch back to workbooks and the top movies 2012 book it will be workbooks one that activate and just again to prove already show you that this works if I drag the window down so we can see Excel in the background and then use f8 to step through this routine when I execute this line we switch in to book two and 1xq the next line we switch back to top movies 2012 there are also a couple of special code words you can use to refer to workbooks one of them is called active workbook and hopefully as a name suggest it's fairly easy to understand is whichever workbook is currently active on screen so if I've written a line of code which activates book two and then i subsequently wanted to do something to that workbook i can use the word active workbook in order to achieve that it's like the active workbook and let's say i wanted to close it down for instance that would then close down book to ask me to save any changes if I made any and then that would also then make top movies 2012 the active workbook and the reason that would happen is because that's the only other work but there's open so in fact if I repeated that line immediately below and selectively were close that line would then close down top movies 2012 so again if I just resize my window so you can see what's happening in the background and then scroll down so we can see the code I'll step through this subroutine step by step using the f8 key so I go to workbook 2 that is the active workbook I press f8 that will close that workbook down I hadn't made any changes which is why I wasn't prompted to save anything if I press f8 again now because top movies 2012 is the active workbook it all subsequently closed down top movies 2012 and because I have made changes to that workbook I'm prompted to save the changes I'm actually gonna click cancel here I don't want to close the workbook down at all and then just to quickly show you that there is an extra couple of arguments for the close method if I type in a space after the other closed method for the active workbook there's an optional argument which allows me to save the changes so if I set that to true and that would close down the workbook and save the changes at the same time so quickly switch back into Excel I'm going to reopen book number two again back to the visual basic editor and if I step through the subroutine this time we'll go to book two which we were already there but it'll be closed down then it will close down top movies 2012 saving the changes one final keyword that you can use to refer to workbooks is this workbook and you might have noticed already in the project Explorer but every workbook that you have open has this little object called this workbook what it refers to is the workbook in which the code you're running is stored so the moment I'm writing all of my code in the top movies 2012 workbook which means if I write into my code a reference to this workbook and I say for instance doc clothes even though I've already activated book 2 this workbook still refers to the workbook in which the code is stored again if I just scroll the window down resize the window so we can see what's happening in the background and scroll down then so you can see the subroutine I'll use f8 to step through so this will activate book too of course but then this workbook dot close will actually close down the top movies 2012 workbook because I made changes to it I'll be prompted to save them if I want to in fact I'll click the Save button and there you can see that the top movies 2012 workbook has been closed if I was writing code inside book 2 then if I use this workbook it will refer to the book 2 workbook instead so we've seen how to refer to in switch between workbooks we already have open now let's see how you will put up a workbook that's currently closed so I close down the book to workbook that I had open earlier on and I'd like to write the line of code which will then open it up the techniques relatively straightforward actually you begin by referring to the workbooks collection again but instead of referring them to a specific workbook you actually apply the open method to the collection now there's a single compulsory argument or parameter and lots of optional ones the only one we have to provide here is the file name now if I can remember where I store this C colon backslash users my name Andrew your gold back slash desktop spell that properly backslash book to dot xlsx okay so it's a bit long-winded to type in of course but if I execute this line of code using f8 to step through you'll see the book 2 becomes open and is also worthwhile noting the book 2 becomes the active workbook as well so whenever you open a workbook that workbook subsequently becomes the active one creating a new workbook is even easier than opening an existing one again I start by referring to the workbooks collection but this time you apply the add method to the collection so if I execute this subroutine I should see that I get a brand new workbook the song called book 4 and it's a simple blank workbook you can also choose to create new workbooks based on templates if you have one saved and in my case I do have a template saved so if I type in a space after the add method it exposes that there's an extra optional argument called template and all you need to do is provide the name of your template I've based mine on my top movies 2012 workbook so I say top movies 2012 dot x l TM that's a macro enabled excel template then if I execute this subroutine again I'll find that I get a new workbook this time based on the top movies workbook and there it is now for a quick look at the various ways that you can save a workbook and the easiest way to do that by far is to save a workbook that has already been saved so it already has a file name and a save location you need to start by referring to the workbook you want to save so I'm going to refer to workbooks book to dot xlsx and I'm simply going to say save inject you with me is that simple if I execute the code that workbook has now been saved it can be a little more fiddly to save a workbook that hasn't yet been saved so one which does not have a name in the location yet what I've done here we've written a simple routine which adds a brand new blank workbook and I know that when I execute that it becomes the active workbook so if I wanted to save it I can say active workbook dot save now if I execute this code by using a fade step through you see that when I execute the line of code to create a new workbook it creates one called book 3 when I press f8 to save it it becomes book 3 dot xlsx at this point now I don't know where the workbook has been saved so what happens when one Excel doesn't know a current name and location is it uses a last saved location for the workbook by default that's my documents when you first open up Excel but they could have been switched if you've saved stuff man you elsewhere then that changes the default location in my case it's actually saved it to the desktop and I can show you that if I use the Windows Explorer just briefly I can show you the book three has been saved onto my desktop so what if I wanted a slightly more or better way to control where my workbook is saved and also to give a file name as well to show you how to specify where you want to save a workbook I've written another simple routine which adds a new blank workbook and again I'm going to say active workbook dot but not just save this time I'm going to choose the save as method instead if I type in a space after the save as method you'll see the full parameter list or argument list and the first argument or parameter is called filename now is true that this is an optional argument if I don't provide a filename then this routine here does exactly the same as this routine does here what I'd like to do however is provide a name for my work but to be saved so it needs to be a full filename and folder path I'm going to make it quite a simple short one C colon backslash test workbook not a very inventive name I'll admit but it's good enough to demonstrate the principle so if I use f8 to begin stepping through this one I'll see I got a new workbook called book for when I save it it becomes test workbook xlsx and to prove that that's gone where I asked it to I can use Windows Explorer look on the C Drive and there it is test workbook xlsx the final thing we'll cover in this video is how you can change the file type when you're saving the file so going to written a small subroutine here which adds a brand-new workbook and applies the save as method to the active workbook but this time is going to try to save it as an Excel sm or a macro enabled file type if I step through this routine what will happen is when I attempt to save it the subroutine will fail and that's because I've specified the filename extension which doesn't match the default file type the default file type as you saw earlier on was a standard Excel workbook xlsx so I'm going to do is end running this subroutine what I need to do is work out how to specify a macro enabled file type and the easiest way to do that I think is to click somewhere on the word save as and then press the f1 key on your keyboard this will launch the context-sensitive help system in Excel and it brings me to the workbook save as method and it shows me there's an argument there called file format it's an optional parameter it explains that there's a list of valid choices that I can see by clicking excel file format if I click on this link here this is a list of all of the valid file formats the one that I want in order to save a macro enabled file is one called Excel open XML workbook macro enabled of course this a bit of a mouthful obviously you wouldn't be able to intuit that I don't think just by a sort of guessing code so that's why the help system is so useful it shows you what the valid options are I can either type that in myself or because I'm feeling a little bit lazy I'm going to right click and copy it and then head back to my code in the marinade somewhere I'll just have to close down the help window that sorry type in a comma after the filename argument moving me on to the file formats argument and then simply paste in what I've just copied so there we go an Excel open XML workbook macro enabled so if I step through this routine again now I'll add another new workbook this song called book 8 and if I hit f8 on this line to execute it it saves the workbook as test workbook dot XLS M so it isn't actually in macro enabled file if you've enjoyed this training video you can find many more online training resources at www-why Zelko UK

Channel: WiseOwlTutorials

Views: 157,869

Rating: 4.9463086 out of 5

Keywords: excel vba, Microsoft Excel (Software), vba, workbooks, wise owl

Id: Mx7a-uj2sZI

Length: 14min 41sec (881 seconds)

Published: Fri Jan 17 2014