In this screen cast, I'm going to tell you all about workbooks. This is going to be similar to the screen cast all about worksheets, but we're working with workbooks instead. So you're going to learn about how to count workbooks. Workbooks.Name, you can provide the name of the ith worksheet. ActiveWorkbook.Name, sometimes you'll open a workbook and you might not necessarily know the name. So this provides the name of the active workbook, and then I'm going to talk about how to close workbooks. You can close workbooks but save changes by using, for example, Workbooks(2).Close SaveChanges:=True. Or if you don't want to save changes, SaveChanges=False. We can add and create workbooks by using the Workbook.Add. So let's go through a couple of these examples in VBA. I'm going to be working through a couple of different files here. On the course website I have a zipped file called Sample Files, and in that folder, I've got five different files. I have opened all five of those. I also have a central file here that I'm going to be working through. So we can use different commands to manipulate the different workbooks. So here I've got one, two, three, four, five, six workbooks open. If you didn't know how many workbooks were open, you can use the Workbooks.Count function. So when we do that, we end up with six. There are six workbooks that are currently open. We can also use the Workbooks.Name, for example, to output the name of the second workbook that’s open right now. Then I can run this, and that is File_1.xlsx. You could also use something like the ActiveWorkbook.Name. So when I run this, it totally depends upon which workbook you activate prior to running the sub routine. So if I go to File_1 here, and then go back to the editor and run F5, it's going to tell me File_1. But if I go to File_5 and then go back to the Visual Basic Editor and run it, it's going to tell me File_5. So the ActiveWorkbook is whatever you have selected most recently. There are a couple ways that we can use VBA code to close workbooks. We can use the Workbooks.Close. So if you make changes but you don't want to save those changes you can always add SaveChanges:=False. So this code will neglect any changes that you've made since the last time you saved that workbook. If you want to make sure that you save those changes you can just make this say true. If you know the name of the workbook that you want to close, you can just do Workbooks and then the name in double quotations. So right now I've got File_4 that's open. So if I run this code, then we close File_4. If I wanted to create a new workbook, I could just use Workbooks.Add. So I just ran that, and I've got a new Book6 here. If you want to save a newly created workbook, you want to save as something, you can use this line. And I just got this from recording a macro. But there should actually be a quotation here. A good way to get the file path is you navigate to where you want to place that newly created file. If I wanted to place it in this Sample Files folder, I could just right-click on Sample Files here and do copy address as text. Then I can paste that, and that's the file path to this folder. So I'm going to add a new workbook here, and I'm going to save that as, test.xlsm, and when I run this, we create a new workbook, and then I saved it as test. If you wish to delete a workbook, I would advise against this, because it's really easy to mess up your code and accidentally delete workbooks that are really important. But if you really need to then I would record a macro if this is really needed. Otherwise you can just clean it up afterwards. You can just manually delete files. Moving back and forth between workbooks, similar to moving back and forth between worksheets. We can use the Activate, so I can do Workbooks("File_1.xlsm').Activate. We can also use the Set command, Set tWB, as an example, equal to ThisWorkbook, and I've got an example that'sgonna show you this in a minute. We can also use aWB to be the ActiveWorkbook, and then we can close all workbooks, and I've got an example of this. So I wanted to show you why it's important to know what you're doing when you're moving back and forth between workbooks. If I just have the generic statement here, MsgBox Range("A1"), this totally depends upon which workbook is the active workbook and which worksheet is the active sheet. So for example, if I just go to this. So this is kind of my main file that I'm working on. If I make this the active workbook and then I go here to VBA, to the editor, and I press F5, it's going to output 7. However, if I select something like File_3 here, and then go to the editor and I press F5, then it's going to give me name. Also, it depends upon which worksheet you are in. So if I go back to this workbook, even though this is the active workbook, the active worksheet is sheet two. And then I when I run this it's going to give me ten. So to remove any ambiguity we can put in the workbook, and then the worksheet, and then range A1. For example, if I want to display range A1 in sheet one of workbook one which is this file, then it doesn't matter if I select file three as my active worksheet. Because when I run this, I've eliminated ambiguity, because I've referred to the first workbook in sheet one of that. Another thing that I like to do when I'm working with multiple workbooks is to set the different workbooks to different variables. So I can set this variable tWB = ThisWorkbook. The ThisWorkbook statement at VBA, just sets whatever workbook this code is in, that's going to be ThisWorkbook. So I can set that equal to this variable, tWB. I can then activate a different workbook, like File_3. I can then set this other variable, aWB, to the active workbook, which will be File_3.xlsx. Then I can message box tWB.sheets, Sheet1. Note that we have our workbook, then the worksheet, and then the range. It won't work, you need all three of these components in order for this to work. And then I could even activate another workbook like File_5. And then even though File_5 is the active worksheet, I can then message box aWB because aWB was set when File_3 was the active workbook. So even though File_3 is no longer the active workbook, I can then go back because I've stored the previously active workbook as the variable aWB, and I'm referring to Sheet1 and range A1. So let's go through this. I can set tWB to this workbook. If you are interested, you can look down here in the locals window, you can open that up to look at the file path. We're going to set aWB to the active workbook, so that's File_3. Then I can message box range A1 of this workbook, which is seven. So that's file, that was back at this workbook which happens to be this one, but sheet one, so that's cell A1. I can continue then, I can activate File_5, and then I can message box File_5 in cell A1 has the word Charlie. But remember, aWB was set when File_3 was the active workbook. So then when I do this, it's going to give me name, because name is what is in cell A1 of File_3. The last thing I wanted to show you is how we can close all workbooks other than the current one, the one that we're working in. So I'm going to dim w as a Workbook. For each w in the Workbooks collection, If Not w Is ThisWorkbook, w Is ThisWorkbook will either give us a true or false. So if the current workbook is ThisWorkbook, we're not going to do this because that'll be true, but we're doing not. So what this does, it'll close all workbooks other than ThisWorkbook, which is the workbook that this code is in. So right now I've got a bunch of files open and I can just run this sub to close everything. And if you wanted to, if you made changes, then we can just put this SaveChanges:=False. So if I've made any changes to any of those workbooks, it's not going to make any of those changes. And I'm going to press F5, and boom, we've closed all workbooks other than ThisWorkbook that I am currently working with. So that gives you an idea of the basics of workbooks.
