1
00:00:02,160 --> 00:00:08,100
So I mentioned before that it can be a bit tedious to keep typing out the object hierarchy all the time

2
00:00:08,100 --> 00:00:13,780
so typing out application workbooks parentheses example and then dot and whatever you want to do with

3
00:00:13,780 --> 00:00:14,760
our workbook.

4
00:00:14,760 --> 00:00:21,450
So instead one kind of shortcut we can take to dodge having to type all of that out every time is to

5
00:00:21,450 --> 00:00:23,620
create a workbook variable.

6
00:00:23,640 --> 00:00:30,600
So this line here creates a variable I've called it WB 1 as a workbook and so a workbook is a specific

7
00:00:30,600 --> 00:00:36,040
type of variable that we can use that will essentially represent everything up until this period here.

8
00:00:36,090 --> 00:00:41,270
So everything that comes before this and we can save that as or input that variable however we want

9
00:00:41,280 --> 00:00:42,760
to input that variable.

10
00:00:42,780 --> 00:00:48,930
So in this case I want to set this variable equal to my new example workbook which is what I will have

11
00:00:48,930 --> 00:00:50,500
just saved this as.

12
00:00:50,880 --> 00:00:56,790
So it's important to note that with workbook variables or worksheet variables any type of object variable

13
00:00:56,790 --> 00:00:57,880
that you're creating.

14
00:00:58,020 --> 00:01:06,750
You don't just type WB 1 equals and then whatever you want to set it equal to use the set command here

15
00:01:07,140 --> 00:01:10,620
set WB 1 equal to workbooks new example.

16
00:01:10,660 --> 00:01:14,430
So that's gonna be anytime you use objects that sort of thing you're gonna want to use set instead of

17
00:01:14,430 --> 00:01:16,290
just the equal command.

18
00:01:16,290 --> 00:01:20,640
So moving on let's see an example of this in action.

19
00:01:20,760 --> 00:01:27,630
I've created some variables here I've created WB name WB full name and WB file path and those are all

20
00:01:27,630 --> 00:01:32,370
just string variables and all of those are gonna be if we're gonna pull those properties out of our

21
00:01:32,370 --> 00:01:33,450
workbook here.

22
00:01:33,450 --> 00:01:40,740
So instead of typing out applications out workbooks new example I'm just going to type WB one dot name

23
00:01:41,280 --> 00:01:46,560
and when VBA hits this particular piece of code here it will look back up to see what I set this very

24
00:01:46,560 --> 00:01:50,590
wise and it we'll see it is this particular workbook that we're in now.

25
00:01:50,730 --> 00:01:57,480
So WB names should adopt the name of the workbook path will adopt the path of the workbook in the full

26
00:01:57,480 --> 00:02:01,740
name will adopt the full name of the workbook and we'll see the differences between these three in a

27
00:02:01,740 --> 00:02:08,370
minute because we're going to output them all in a really big message box along with C Now C is just

28
00:02:08,370 --> 00:02:10,050
the count of the workbook.

29
00:02:10,050 --> 00:02:15,540
So we created a little integer workbook that we have here and then we've told it just count all of the

30
00:02:15,540 --> 00:02:19,740
workbooks that I have opened right now and in theory we should only have one workbook open.

31
00:02:20,010 --> 00:02:25,200
So this should show up as just one if you have multiple workbooks open when you run this code let's

32
00:02:25,200 --> 00:02:29,550
say you have three other workbooks open then this would show up and count however many you have say

33
00:02:29,550 --> 00:02:31,930
three or four and show that number instead.

34
00:02:32,570 --> 00:02:38,970
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

35
00:02:38,970 --> 00:02:44,130
my workbooks count and I'm going to make a little message box here that'll count how many workbooks

36
00:02:44,130 --> 00:02:44,960
that we have.

37
00:02:45,090 --> 00:02:51,000
But in this case I will have just added a new workbook and so we add workbooks in the same way that

38
00:02:51,000 --> 00:02:53,950
we just set our variable up here.

39
00:02:54,090 --> 00:02:59,010
But instead of referencing a particular workbook instead of saying workbook is new example or something

40
00:02:59,010 --> 00:03:02,450
like that we're going to use workbook that add.

41
00:03:02,640 --> 00:03:08,910
Now when we use that add Excel is going to add a workbook and a new file opened it up it will default

42
00:03:08,940 --> 00:03:14,310
the name of the workbook it will default the name of the sheets and it will automatically become your

43
00:03:14,310 --> 00:03:15,650
active workbook.

44
00:03:15,810 --> 00:03:19,890
So when you add a new workbook it's important to note that that workbook will automatically be your

45
00:03:19,890 --> 00:03:20,630
active workbook.

46
00:03:20,640 --> 00:03:26,760
Anything you do from that point forward unless you change your active workbook will be in this particular

47
00:03:26,880 --> 00:03:33,510
new work but that you just added So after we've added this workbook when we recount our workbooks we

48
00:03:33,510 --> 00:03:40,150
should see this number go up by 1 compared to whatever our C was before this next line is going to activate

49
00:03:40,150 --> 00:03:46,540
our workbook one then activate our workbook 2 and I'm going to show you this step by step by putting

50
00:03:46,540 --> 00:03:52,030
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

51
00:03:52,360 --> 00:03:57,280
debugging is we'll talk about that in the debugging video but how I triggered this I just click to the

52
00:03:57,280 --> 00:04:04,180
left of this line into this gray bar here and we can see this red circle shows up and what that means

53
00:04:04,210 --> 00:04:09,310
is that VBA will actually pull us on this line and let us interact with our file or interact with our

54
00:04:09,310 --> 00:04:10,960
code or whatever we want to do here.

55
00:04:10,960 --> 00:04:15,640
So in this case we'll be walking step by step through these two so that we can see the difference between

56
00:04:15,640 --> 00:04:21,070
activating our workbook one and activating our workbook to the last thing I'm going to do I'm going

57
00:04:21,070 --> 00:04:26,170
to save this workbook we can see the only difference between this save as line and the Save As line

58
00:04:26,170 --> 00:04:33,940
up here is that I've saved this as a default workbook instead of a macro enabled workbook and this time

59
00:04:34,330 --> 00:04:39,850
I've gone ahead and I've given it a password and this password was put into the right red password which

60
00:04:39,850 --> 00:04:45,400
means when I open this file Excel should give me the opportunity to input a password and if I can't

61
00:04:45,400 --> 00:04:52,580
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

62
00:04:52,580 --> 00:04:59,180
I've closed this workbook now if I hit space here we can see we have a couple of choices here You can

63
00:04:59,180 --> 00:05:04,970
close the you can choose whether or not you want to save the changes that you made in the workbook in

64
00:05:04,970 --> 00:05:09,800
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

65
00:05:09,800 --> 00:05:14,150
go ahead and change that to true if you want to save any changes you're trying to for example write

66
00:05:14,150 --> 00:05:18,800
a macro that would close all of the workbooks you have opened at once then you might want to use true

67
00:05:18,800 --> 00:05:24,080
there to make sure that all of those changes that you made got captured last thing we're going to do

68
00:05:24,080 --> 00:05:30,080
is open up the workbook that we just closed and I'll show you here that we can input the correct password

69
00:05:30,110 --> 00:05:35,450
so we're going to open up this workbook again this is the same workbook that we just closed note that

70
00:05:35,450 --> 00:05:40,970
when we open the workbook when we input the file name we do also need to reference the file extension

71
00:05:40,970 --> 00:05:47,980
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

72
00:05:47,980 --> 00:05:54,340
not concerned about the format and we don't have a regular password on this if you remember we put a

73
00:05:54,520 --> 00:05:55,930
right rez password on it.

74
00:05:55,930 --> 00:06:00,340
So it's important that you match the correct location of the password.

75
00:06:00,340 --> 00:06:05,560
So in this case if it was a password protected file a regular password protected file you'd put the

76
00:06:05,560 --> 00:06:11,290
password into this input here but in our case we made this a right rez password protected file which

77
00:06:11,290 --> 00:06:15,430
again means that we can put the password or we can just open it read only.

78
00:06:15,430 --> 00:06:18,160
And so that's where we need to put this particular password.

79
00:06:18,190 --> 00:06:23,360
And again we made the password password so we'll just make that same input here.

80
00:06:23,390 --> 00:06:27,250
You can also jump right over the read only recommended if we wanted to.

81
00:06:27,260 --> 00:06:31,520
So we saved this file up here it is read only recommended and if we wanted to jump over that we could

82
00:06:32,360 --> 00:06:39,120
and these again these front inputs are the ones that you will mainly interact with so now we can go

83
00:06:39,120 --> 00:06:44,210
ahead and test run this code again we can see I don't have any other files saved into here.

84
00:06:44,400 --> 00:06:52,100
I'll go ahead and go to my Excel file under macros I like test code and click Run.

85
00:06:52,210 --> 00:06:57,580
Now the first message box we get should be the name property of the workbook which is new example that

86
00:06:57,640 --> 00:06:58,690
Excel S.M..

87
00:06:58,930 --> 00:07:02,450
So we can see this is remember we saved this as new example.

88
00:07:02,470 --> 00:07:08,050
So this is the new example workbook and it is a dot Excel S M for a macro enabled.

89
00:07:08,080 --> 00:07:10,840
We know our save probably worked properly but we'll check in a second.

90
00:07:11,440 --> 00:07:13,390
And this is what the name property looks like.

91
00:07:13,450 --> 00:07:16,540
Of the workbook it includes the file extension.

92
00:07:16,600 --> 00:07:21,370
So if you ever tried to get just down to this root name you have to play a little bit of games with

93
00:07:21,370 --> 00:07:26,950
the string properties to try to eliminate that file extension and we'll show you how to do that in a

94
00:07:26,950 --> 00:07:28,980
string properties video later on.

95
00:07:29,170 --> 00:07:36,280
This is the path variable and you can see this doesn't actually show the file name or anything like

96
00:07:36,280 --> 00:07:37,510
that on the end of it.

97
00:07:37,570 --> 00:07:42,610
It just shows the exact path of where this file was saved to this line.

98
00:07:42,610 --> 00:07:45,790
Here is the full name property.

99
00:07:45,820 --> 00:07:49,300
And this includes both the file path and the file name.

100
00:07:49,660 --> 00:07:53,670
So this is a combination of these two properties that we looked at before.

101
00:07:53,680 --> 00:07:56,420
So these three properties represent different things.

102
00:07:56,500 --> 00:07:57,580
They're all useful.

103
00:07:57,580 --> 00:08:02,050
They all have different cases where you might use one over the other but it's important to know exactly

104
00:08:02,050 --> 00:08:04,560
what they represent within VBA.

105
00:08:04,570 --> 00:08:09,370
The last thing that shows up here is one which is our workbooks count that we did initially.

106
00:08:09,430 --> 00:08:12,160
So we can see right now I only have one workbook open.

107
00:08:12,160 --> 00:08:14,480
It is only this new example workbook.

108
00:08:14,700 --> 00:08:21,080
Now I'm going to go ahead and click ok we can see that VBA has stopped on this message box because I

109
00:08:21,080 --> 00:08:24,010
put this little red dot out in the side here.

110
00:08:24,380 --> 00:08:29,330
So I'm going to make this VBA window a little bit smaller.

111
00:08:29,630 --> 00:08:32,240
I'll put it on the right and I'll put this on the left.

112
00:08:33,260 --> 00:08:38,290
So now when I hit F eight on my keyboard it will move to the next line.

113
00:08:38,300 --> 00:08:43,440
You can also do it from up here in debug step into right here.

114
00:08:43,550 --> 00:08:48,860
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

115
00:08:48,890 --> 00:08:54,900
this message box line and we can see now it's reading too and this is how many workbooks we have open

116
00:08:54,900 --> 00:08:55,740
now.

117
00:08:55,740 --> 00:09:03,300
And this makes sense because we just added a new workbook so I'll click OK now WB one that activate

118
00:09:03,300 --> 00:09:09,690
is going to make sure I have this particular notebook open this new example notebook that we have.

119
00:09:09,780 --> 00:09:11,130
So I'll hit at eight.

120
00:09:11,190 --> 00:09:15,390
We can see nothing changes because this was already open so already my active workbook.

121
00:09:15,450 --> 00:09:21,360
Now if I hit eight again we'll see this is changed to my workbook 2 which is this workbook that we just

122
00:09:21,450 --> 00:09:22,190
added.

123
00:09:22,230 --> 00:09:26,250
So this is what it means to activate different workbooks is the same thing that we showed earlier in

124
00:09:26,250 --> 00:09:29,600
the object hierarchy video on activating certain sheets.

125
00:09:29,600 --> 00:09:34,650
So it's important to pay attention to what is activated at the time that you are running particular

126
00:09:34,650 --> 00:09:41,310
lines of your code not just walking through the rest of this I'll hit F eight again and I should know

127
00:09:41,310 --> 00:09:47,820
that my file was just saved as the new workbook which is just the default workbook will hit it again

128
00:09:48,270 --> 00:09:55,170
and it should close my workbook so we'll do this so we can see that that just closed successfully.

129
00:09:55,300 --> 00:09:59,770
And then the last thing it should do is open that back up and it should automatically feed our password

130
00:09:59,770 --> 00:10:02,460
into the correct input when we open.

131
00:10:02,550 --> 00:10:08,380
So I'll hit it again and we can see it has successfully opened up my new workbook here.

132
00:10:08,470 --> 00:10:14,440
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

133
00:10:14,440 --> 00:10:17,760
save this just in case and close out.

134
00:10:17,890 --> 00:10:21,600
Now we can see I have all three of my files here which is what I expected.

135
00:10:21,640 --> 00:10:26,160
If I open my new file we can see it asks for a password.

136
00:10:26,670 --> 00:10:30,030
And this is password for right access or I can open it read only.

137
00:10:30,030 --> 00:10:34,170
So this is what I was explaining earlier when if I don't know the password to this file I can go ahead

138
00:10:34,170 --> 00:10:40,050
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

139
00:10:40,050 --> 00:10:46,720
input the password then I would be able to make edits if I open up this new example file we can see

140
00:10:46,720 --> 00:10:52,310
it does recommend to me it says you daughter would like you to open this as a read only file unless

141
00:10:52,310 --> 00:10:55,210
you need to make changes it doesn't ask for a password.

142
00:10:55,210 --> 00:11:00,760
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

143
00:11:00,760 --> 00:11:10,170
way that you could get to that particular setting is if you go to file save as browse under Tool's general

144
00:11:10,170 --> 00:11:17,400
options and you can see here this is this read only recommended box is checked so we could uncheck that

145
00:11:17,430 --> 00:11:19,500
if we wanted to get rid of that particular property

146
00:11:22,650 --> 00:11:30,420
so in this video we talked about the workbook objects in excel we covered how we can interact with that

147
00:11:30,420 --> 00:11:31,760
object through VBA.

148
00:11:31,770 --> 00:11:37,470
We covered the main actions and properties we use in VBA regarding workbook objects and we gave examples

149
00:11:37,470 --> 00:11:39,660
of how to apply those in VBA.