WEBVTT

00:02.160 --> 00:02.940
Okay guys.

00:02.940 --> 00:04.160
And welcome back.

00:04.170 --> 00:10.130
Now just to catch you up I've just added a couple of shapes on here so I've come to insert shapes I've

00:10.140 --> 00:11.780
literally just picked this one.

00:11.820 --> 00:16.000
I think I have dragged to shape some here called them single and multiple.

00:16.080 --> 00:23.670
I have assigned the macro which macro I signed over signed the macro create single folder structure

00:23.730 --> 00:31.010
to this button and I have assigned the macro create multiple folder structure to this shape you see.

00:31.020 --> 00:33.230
So now you're fully caught up.

00:33.270 --> 00:40.230
So what this is going to do is it is going to have a look at this column here and it's going to create

00:40.240 --> 00:46.800
say it create a folder in our specified path one call ms1 call Ashley 1 call Joe 1 called Banni.

00:46.800 --> 00:53.150
One called Danny and create a hyperlink to the very same folder here in column B.

00:53.190 --> 00:55.830
So let's have a look at that code.

00:55.830 --> 01:02.330
Now for the structure this tab here has a Visual Basic name of S H index.

01:02.370 --> 01:07.010
So our first action is to call this sheet to make sure we're on the right sheet.

01:07.020 --> 01:13.920
So share index dots select range B for select selects this cell and this is where we're going to be

01:13.920 --> 01:20.880
writing a hyperlink and it is going to be looking to the left to column A to retrieve the fall.

01:20.880 --> 01:22.600
The mound that we want to create.

01:22.650 --> 01:30.310
Okay so here we have a while wende loop and it says while the active sell off says zero no rows.

01:30.330 --> 01:38.430
Minus 1 1 column to the left column a dot value doesn't equal blank continue so it's gone a while and

01:38.430 --> 01:43.180
when until it gets to here then it's going to look here sace blank and the code is going to stop.

01:43.190 --> 01:43.990
Execute him.

01:44.070 --> 01:44.990
Okay.

01:45.020 --> 01:53.760
Inside while when gloop we have this instruction here on Error Resume Next And what that does is if

01:53.760 --> 02:00.140
the code errors is going to force it to continue anyway only for this line of code.

02:00.150 --> 02:04.150
Because here we're going to disable that error trap.

02:04.200 --> 02:10.270
Now the reason we don't want code to stop here is if it turns out we already have a folder called AM.

02:10.290 --> 02:13.670
We don't want it to stop and not complete Ashley Joe.

02:13.660 --> 02:18.600
Bernie and Danny wanted to carry on even though Amma may already exist.

02:18.600 --> 02:22.940
So here is that line of code the magic line of code that actually does the job.

02:22.990 --> 02:25.490
M.k. dahr make directory.

02:25.650 --> 02:33.420
You can see here in my instance I have see uses Allen test out my folders backslash and then I am king

02:33.420 --> 02:38.630
catinat in that with the and and same whatever the active cells are set.

02:38.640 --> 02:44.640
0 rows minus 1 columns column a dot value equals.

02:44.700 --> 02:49.300
So my path backslash and whatever word is in here.

02:49.410 --> 02:55.620
So it could be it could be names of people it could be names of corporations names of products and names

02:55.620 --> 02:56.550
of projects.

02:56.550 --> 03:01.890
Why have you why create a false structure for so and then we're going to get them to this way.

03:01.890 --> 03:07.450
We're going to disable the error trap that we've just put there that is going to build our fault so

03:07.450 --> 03:08.880
let's just step through the code.

03:08.880 --> 03:10.950
Up until this point is here.

03:10.950 --> 03:18.510
So I'm just going to hit debug and run its cursor and over here you can see that Emma has just been

03:18.600 --> 03:19.130
added.

03:19.260 --> 03:22.110
I'm struggling to get all three things into view here.

03:22.290 --> 03:28.590
So one of the DOS or out will go through the loop once and then I'll just run the code and have this

03:28.590 --> 03:32.060
window visible so you can actually see happening in real time.

03:32.310 --> 03:41.210
Okay so active sheet done hyperlinks does and anchor equal selection address exactly the same path concatenate

03:41.490 --> 03:46.010
with exactly the same name which is actually self offset minus one.

03:46.050 --> 03:50.800
So essentially it's just the same path exactly that we specified above.

03:50.860 --> 03:58.380
Yeah but we're adding a hyperlink to the cell that we are in and the text to display is go then.

03:58.390 --> 04:02.620
So notice what happens over here when I hit F 8.

04:02.640 --> 04:09.860
Boom we have the hyperlink and if I were to click the hyperlink a go this folder opens up this piece.

04:09.900 --> 04:13.010
Which is C uses Alan dressed up my folders.

04:13.180 --> 04:20.280
Emma again I am just going to close that and here or we can say a sell off set here to sell off that

04:20.280 --> 04:22.140
move down 1 and repeat.

04:22.140 --> 04:25.340
So I'm just going to hit stop that is assigned to this.

04:25.350 --> 04:34.850
This shaped list here so I'm just going to drag this in and then when I hit play or hit the button.

04:36.430 --> 04:37.270
There they are.

04:37.300 --> 04:43.750
And you'll notice that we have Emma actually already and Emma so Emma just continue so all of the names

04:43.750 --> 04:51.010
here now have a fall that in my fall there's and if I click on the hyperlink for Emma.

04:51.040 --> 04:52.310
Emma folder opens up.

04:52.330 --> 04:57.630
And if I click on the hyperlink for Ashley WIPs overcome let's say Who's that.

04:57.640 --> 05:00.760
Joe Joe opens up and it's as simple as that.

05:00.760 --> 05:07.690
So from that one base line of code and skim delete those and from that one baseline of code we've mange

05:07.690 --> 05:14.780
to build a routine that can automatically create an entire direct treat structure of folders.

05:14.980 --> 05:17.460
OK see you in the next let you way we're going to look.

05:17.470 --> 05:18.790
And how similar.

05:18.790 --> 05:23.450
So we can have folders within folders and it's a lot simpler than you may think.

05:23.510 --> 05:25.450
Okay I'll see you in the next lecture.
