 and welcome back to another video. So this video is going to focus around looking at the basics of using Excel VBA within one workbook to open another workbook, enter some information and then close and save that other workbook. So it's going to be just going over the basics today, so I'm going to go in too deep but again it's another one just wants to spend a bit of time going on to like just the basics how this would work. Obviously it will give you a great feature that you can obviously go off and start using this with other projects or scenarios that you have at the moment but obviously we'll build upon this in time so we actually sort of see the real benefits of opening a workbook and being able to extract data from that workbook. But today's video we're literally going to look at the basics of like I say opening another workbook, entering some information and then just closing and saving that workbook. So the first thing we're going to need to do is we're open up our Excel so just open up a new Excel workbook here. The VBA developer pane opened here as well so we've gone into a new module and I was going to just create a subroutine and we're going to call this sub and we'll call it workbook open and close and just use some spaces like that. Okay so the first thing we need to do is we obviously need to open our workbook and well even before that we need to know where the workbook is. So on another screen here I'll just put on just so you can see I've opened up the explorer and you can see I've got this demo workbook here and you can see there's the file path so I'll obviously be copying that out in a second and also if I just open up this workbook and it's probably going to open up on another screen yeah here we go you can see that this workbook is currently empty so there's nothing in there but this is what we'll be using for populating some information. So we'll just close that for time being and we'll move this explorer pane over to the side there. Okay so the first thing we need to do is we're going to go workbooks so let's just type this workbooks and when you hit full stop you can see that we have a very number of options available to us so again some of these other ones we'll look at in time but the option we want is open and you could scroll down this to find open here or all we need to do is just type the word open and that'll do exactly the same for us and you see as soon as it's space it's put the capital O there so it's know it's reformatted and that value's been accepted. We're then going to enter or type file name followed by a colon equal sign and then within quotations this is where we now need to enter the actual the full path of that file so what I just need to do is if I go over and I find or copy this from the explorer and paste that into there you can see this is the actual file path for that document but we need to go one step further like say put the full path for our particular file so I just need to add on to the end of this another backslash and I just need to type the workbook name which is going to be or is demo underscore book and also just put the file extension so for us it is xlsx so again you just need to make sure you have the correct file extension for the file that you're using do enter just go to a new row so we're now defined okay we want to open a workbook and this is the file name of that workbook so next part is to then define well actually is to now actually put some information into this workbook so we're just going to again go workbooks and we'll go open and we're going to call this demo book because we're now referring to that actual workbook that we wanted dot xlsx quotations close brackets so this first one is we're basically kind of defining and opening this workbook and this part here we're now referring to that workbook that we've got open so obviously once it's been open we know they've got this workbooks that we can utilize or work with once we've done that we're still going to do dot sheets so this bit will be fairly familiar with you reducing our previous videos so when we're obviously referring to a range and actually let me just type this in first and then that will probably become more applicable to sheets one dot range and we're actually going to just go into a one here and we're going dot value equals xl so what I was only going to touch on is this part of the actual of the script here will seem very familiar to you'd like to say if you've seen our previous videos because when we're referring to another sheet or just we've been adding information into our sheets obviously we always have to refer to our sheet or the sheet name or sheet reference we'll then also enter the range obviously where we want to do it and then dot value to enter text you'd have seen in previous videos that I'll always put in here like the actual text so we'd have gone sheet one so this would work fine for us but all I'm just going to do because when we're referencing another work but we don't always know what the sheet might be called or alternatively people might have renamed the sheet without us knowing so therefore in this exam bottom is going to be using the sheet index number and all this is is this is just the index number that's assigned to each sheet so as you see at the moment we've got one sheet in this workbook called sheet one the index for this sheet would be one if we were to enter another sheet again regardless of what we called it it would then be called or referred to as sheet number two so that's another way that you can reference sheets but like I say this part of the script here should seem very familiar to you and this part here is just sort of a last ball a new adultery say because we're referring to a different workbook to the one we're currently working in so this part here we've now actually entered a value into that workbook so what we now need to do is go on to another new line and we're going to just now save that workbook so we just now did the same reference so we're going to work books open brackets at demo underscore book dot xlsx close quotations dot save so that is obviously really simple if you wanted to save the file as something else then obviously you could just select the save as a button and obviously allow you to save it as a different file name and separate from the one you're using that's obviously a really good technique again we'll probably cover off in the future but that just enables you to maybe use this demo book as maybe a template and then when you do save that workbook you're saving it is obviously another file name so you're never overriding the template just to give an example as why you might want to use that and the last part so once we've added obviously our text we've saved the workbook so the last thing we need to do here is obviously close the workbook so we're going to work books do the demo underscore book obviously you could have copied this if that was easier and probably quicker dot close and that is it that's the entirety of the script that we need to perform this function so I'm just going to do that so you can just see just obviously push the tab button there to move it in so yeah just to recap obviously we're defined and given the reference for what where the file is we'll then ask the the script to say okay well with this book we want you to set the sheet number one in range or cell a1 we want the value to be excel once you've done that we then want to save the workbook and lastly we want to close the workbook so with all that done let's see if it works so what I'm going to do is click within our sub routine and hit f5 and you'll see on that whereas on a separate screen from me so we won't have seen it but you're just seeing the loading there is you may see the screen well you'll see another workbook gradually or quick very quickly open and then close that's obviously as this action has been performed if I'm then to go into our excel file so let me find our demo book and I assure you I'm not now entering this in if it does if it does open another screen no so there you can see it's now been put into and it's now put the value of excel into range a1 for us so we can successfully say that that's now being updated so let's just change that and go back into here and let's say we don't want to just do sell a1 I'm just checking that file is closed yeah say we don't want to just do sell a1 we want to do this for maybe um I don't know from a1 all the way through to a or even scope b b1000 so you can see again it's going to do a lot of um it's going to do a lot of excels basically in all this range and it just goes to show you the power of doing this um I can't see why there'll be an example why you want to put the word excel or just one word into all of these sales uh but in my hate I think this is probably a good another scenario just to show you the benefits and obviously the volume that this can do for you so if we were to go run this again now you can see it take a bit longer there if I now go into our file open up demo book and wait for it to open yeah and then pull this across you can now see that the word excel has been obviously populated into all of those cells all the way down to row 1000 so obviously that's been updated for us and that gives you insight on how to open another workbook from your existing workbook and to populate it with some information so as I did say a bit of a simple example there you can all obviously play around with this uh for our own sake let me say let me go to the workbook and let me say we want to now change this value to um I don't know uh Monday random day of the week here and let's say we want to quote out those two lines of code what we can do is if you now run this it will populate the file for you but it won't say even close it so this is another way you can play around to see what's happening with the code and it helps you to see what's going on uh like I say as you run that's obviously what happens if you comment those two out so if you comment these two out the file will open and it will populate but it won't actually close and save it for you obviously you just need to take those two off if you do want to revert back to that so as I said at the beginning of the video a very simple example we're going to cover off today to give you an insight into how to open another workbook and populate it with some information as you can probably see as we step down the line you go into more advanced examples the benefits of this would be obviously being able to maintain multiple excel sheets uh updating and extracting information from those sheets as required so if you did enjoy that video and you learned something new please do give the video a like uh greatly appreciated by myself and also does help that all important youtube algorithm if it's your first time coming across the channel and watching one of our videos or you have watched our videos before and you've not yet subscribed to the channel please do subscribe to the channel uh like I said again I would be greatly appreciated and it does also help the channel to get promoted so that more people can see our content and when you do subscribe make sure you hit that bell notification button so you're notified of all of our future videos so lastly thank you very much for watching and we'll see you in the next video