So I mentioned before that it can be a bit tedious to keep typing out the object hierarchy all the time
so typing out application workbooks parentheses example and then dot and whatever you want to do with
our workbook.
So instead one kind of shortcut we can take to dodge having to type all of that out every time is to
create a workbook variable.
So this line here creates a variable I've called it WB 1 as a workbook and so a workbook is a specific
type of variable that we can use that will essentially represent everything up until this period here.
So everything that comes before this and we can save that as or input that variable however we want
to input that variable.
So in this case I want to set this variable equal to my new example workbook which is what I will have
just saved this as.
So it's important to note that with workbook variables or worksheet variables any type of object variable
that you're creating.
You don't just type WB 1 equals and then whatever you want to set it equal to use the set command here
set WB 1 equal to workbooks new example.
So that's gonna be anytime you use objects that sort of thing you're gonna want to use set instead of
just the equal command.
So moving on let's see an example of this in action.
I've created some variables here I've created WB name WB full name and WB file path and those are all
just string variables and all of those are gonna be if we're gonna pull those properties out of our
workbook here.
So instead of typing out applications out workbooks new example I'm just going to type WB one dot name
and when VBA hits this particular piece of code here it will look back up to see what I set this very
wise and it we'll see it is this particular workbook that we're in now.
So WB names should adopt the name of the workbook path will adopt the path of the workbook in the full
name will adopt the full name of the workbook and we'll see the differences between these three in a
minute because we're going to output them all in a really big message box along with C Now C is just
the count of the workbook.
So we created a little integer workbook that we have here and then we've told it just count all of the
workbooks that I have opened right now and in theory we should only have one workbook open.
So this should show up as just one if you have multiple workbooks open when you run this code let's
say you have three other workbooks open then this would show up and count however many you have say
three or four and show that number instead.
So moving on I'm going to do the same thing I'm going to create a b integer and B is also going to be
my workbooks count and I'm going to make a little message box here that'll count how many workbooks
that we have.
But in this case I will have just added a new workbook and so we add workbooks in the same way that
we just set our variable up here.
But instead of referencing a particular workbook instead of saying workbook is new example or something
like that we're going to use workbook that add.
Now when we use that add Excel is going to add a workbook and a new file opened it up it will default
the name of the workbook it will default the name of the sheets and it will automatically become your
active workbook.
So when you add a new workbook it's important to note that that workbook will automatically be your
active workbook.
Anything you do from that point forward unless you change your active workbook will be in this particular
new work but that you just added So after we've added this workbook when we recount our workbooks we
should see this number go up by 1 compared to whatever our C was before this next line is going to activate
our workbook one then activate our workbook 2 and I'm going to show you this step by step by putting
a brake on this line and it's it's OK if you don't understand what this brake is or what step by step
debugging is we'll talk about that in the debugging video but how I triggered this I just click to the
left of this line into this gray bar here and we can see this red circle shows up and what that means
is that VBA will actually pull us on this line and let us interact with our file or interact with our
code or whatever we want to do here.
So in this case we'll be walking step by step through these two so that we can see the difference between
activating our workbook one and activating our workbook to the last thing I'm going to do I'm going
to save this workbook we can see the only difference between this save as line and the Save As line
up here is that I've saved this as a default workbook instead of a macro enabled workbook and this time
I've gone ahead and I've given it a password and this password was put into the right red password which
means when I open this file Excel should give me the opportunity to input a password and if I can't
I should still be able to open the file it'll just be read only if I come down to this line we can see
I've closed this workbook now if I hit space here we can see we have a couple of choices here You can
close the you can choose whether or not you want to save the changes that you made in the workbook in
this case I've said false because I just saved it so I know I don't need to save it again but you can
go ahead and change that to true if you want to save any changes you're trying to for example write
a macro that would close all of the workbooks you have opened at once then you might want to use true
there to make sure that all of those changes that you made got captured last thing we're going to do
is open up the workbook that we just closed and I'll show you here that we can input the correct password
so we're going to open up this workbook again this is the same workbook that we just closed note that
when we open the workbook when we input the file name we do also need to reference the file extension
that we used I'm not going to use any update links or anything it's not a read only so it's fine we're
not concerned about the format and we don't have a regular password on this if you remember we put a
right rez password on it.
So it's important that you match the correct location of the password.
So in this case if it was a password protected file a regular password protected file you'd put the
password into this input here but in our case we made this a right rez password protected file which
again means that we can put the password or we can just open it read only.
And so that's where we need to put this particular password.
And again we made the password password so we'll just make that same input here.
You can also jump right over the read only recommended if we wanted to.
So we saved this file up here it is read only recommended and if we wanted to jump over that we could
and these again these front inputs are the ones that you will mainly interact with so now we can go
ahead and test run this code again we can see I don't have any other files saved into here.
I'll go ahead and go to my Excel file under macros I like test code and click Run.
Now the first message box we get should be the name property of the workbook which is new example that
Excel S.M..
So we can see this is remember we saved this as new example.
So this is the new example workbook and it is a dot Excel S M for a macro enabled.
We know our save probably worked properly but we'll check in a second.
And this is what the name property looks like.
Of the workbook it includes the file extension.
So if you ever tried to get just down to this root name you have to play a little bit of games with
the string properties to try to eliminate that file extension and we'll show you how to do that in a
string properties video later on.
This is the path variable and you can see this doesn't actually show the file name or anything like
that on the end of it.
It just shows the exact path of where this file was saved to this line.
Here is the full name property.
And this includes both the file path and the file name.
So this is a combination of these two properties that we looked at before.
So these three properties represent different things.
They're all useful.
They all have different cases where you might use one over the other but it's important to know exactly
what they represent within VBA.
The last thing that shows up here is one which is our workbooks count that we did initially.
So we can see right now I only have one workbook open.
It is only this new example workbook.
Now I'm going to go ahead and click ok we can see that VBA has stopped on this message box because I
put this little red dot out in the side here.
So I'm going to make this VBA window a little bit smaller.
I'll put it on the right and I'll put this on the left.
So now when I hit F eight on my keyboard it will move to the next line.
You can also do it from up here in debug step into right here.
But I'm just going to use it eight on my keyboard so I'll hit F eight once and we'll see VBA execute
this message box line and we can see now it's reading too and this is how many workbooks we have open
now.
And this makes sense because we just added a new workbook so I'll click OK now WB one that activate
is going to make sure I have this particular notebook open this new example notebook that we have.
So I'll hit at eight.
We can see nothing changes because this was already open so already my active workbook.
Now if I hit eight again we'll see this is changed to my workbook 2 which is this workbook that we just
added.
So this is what it means to activate different workbooks is the same thing that we showed earlier in
the object hierarchy video on activating certain sheets.
So it's important to pay attention to what is activated at the time that you are running particular
lines of your code not just walking through the rest of this I'll hit F eight again and I should know
that my file was just saved as the new workbook which is just the default workbook will hit it again
and it should close my workbook so we'll do this so we can see that that just closed successfully.
And then the last thing it should do is open that back up and it should automatically feed our password
into the correct input when we open.
So I'll hit it again and we can see it has successfully opened up my new workbook here.
I'm going to go ahead and close out of all of these boxes on this one as well and I'll go ahead and
save this just in case and close out.
Now we can see I have all three of my files here which is what I expected.
If I open my new file we can see it asks for a password.
And this is password for right access or I can open it read only.
So this is what I was explaining earlier when if I don't know the password to this file I can go ahead
and just open it read only and it'll open the file for me but I won't be able to make any edits if I
input the password then I would be able to make edits if I open up this new example file we can see
it does recommend to me it says you daughter would like you to open this as a read only file unless
you need to make changes it doesn't ask for a password.
So if I click No it it'll go ahead and let me in in such a way that I can make changes and the other
way that you could get to that particular setting is if you go to file save as browse under Tool's general
options and you can see here this is this read only recommended box is checked so we could uncheck that
if we wanted to get rid of that particular property
so in this video we talked about the workbook objects in excel we covered how we can interact with that
object through VBA.
We covered the main actions and properties we use in VBA regarding workbook objects and we gave examples
of how to apply those in VBA.