File so what he wanted to know was is there a way to unzip a file using vba and there certainly is the code we need to write to do this isn't particularly complicated but it's not immediately.
Obvious how we get started so let's take a look and see what we can do to get started i've created a new excel workbook saved it as a macro enabled file and then in the visual.
Basic editor inserted a module and started a new subroutine i've got this workbook stored in the same folder as the zipped folder that we're going to unzip my example is.
Highly exciting it contains a list of three empty text files i'll let you provide your own zip file for this example hopefully you have a more exciting example to work with than.
The one i have also stored in the same folder as the excel workbook is a regular folder called unzipped files which is currently empty set up ready to receive the files.
Hopefully then better explain what's going on i've set a reference using the tools references option from the visual basic editor i've referenced the microsoft shell controls and.
Automation library so the first thing we're going to do with that library is declare a variable which can hold a reference to a shell object so i'm going to say dim sh as.
Shell 32 which is the name of the library and then the shell class in there then i want to create a new instance of the shell class so i'm going to say set.
Sh equals new shell 32 dot shell now there are lots of interesting things we can do with a shell object and just in case you're interested in some further reading i've got a link to share.
With you from the microsoft docs site the scriptable shell objects article i'll drop this link in the video description so you can find it more easily.
So shell objects like you do all sorts
Of useful things like accessing the file system launching programs changing system settings of course in this video we only really.Care about the file system techniques so the first method we're going to use from the shell is called namespace and this lets us get a reference to a folder.
Object so we're going to create a couple of variables which can hold references to folder objects and then use the namespace method to get references to them.
So let's head back to the visual basic editor and i'm going to declare two new variables let's say dim destination folder as shell 32 dot.
Folder and then we'll also if i just copy and paste that i have a source folder variable which will refer to the zipped folder so once i've created my new instance of.
The shell class i can say things like set destination folder equals sh.namespace and then in some round brackets pass in.
The path to the folder i want to get a reference to so this one's going to get a reference to my unzipped folder which is stored in the same path as this workbook so i'm.
Going to refer to this workbook.path followed by an ampersand and then a backslash and this is unzipped and i'll try to spell that correctly unzipped files.
Close the double quotes and the round brackets i can then do essentially the same thing to get a reference to the zipped folder so again i'm going to copy and paste.
That
And then i'll replace destination folder with source folder and then this workbook.path and the name of the zipped folder is.Called zippedfiles.zip at the end so having done that we could just give the routine a quick test run to make sure that it doesn't fail.
And assuming we don't see any failures that's a good sign so now that we have references to the two folders we need we can simply copy all of the files from the source folder.
Into the destination folder and to do that we're going to make use of the copy here method of the destination folder and the items method of the source folder.
Again just for your reference we've got some documentation to help with that so here's the reference to the folder object for the shell class and you can see all of the methods and properties of.
That object here so we're going to use copy here and items and again we have further documentation that explains how each of those techniques works so the basic code we need back in the.
Visual basic editor is we need to refer to our destination folder and apply the copy here method to that if we type in a space we can see there's a single compulsory parameter which is a.
Reference to the item or items we want to copy and the items i want to copy are from the source folder and the items method okay so having done that that's.
Basically all the code we need if we just have a quick check in the unzipped files folder that's currently empty but if we run this subroutine have a look back at the unzipped files.
Folder we'll see that all three text files have been unzipped into it one potential problem with this technique occurs when our destination folder already contains files with the.
Same name just to demonstrate that let's try to run our subroutine again so this time we get prompted we have to choose what to do with the files with the same name.
I'm going to click the replace the files in this destination option so that will copy over the existing examples now it would be nice if we could automate that and we can by passing the.
Value to the second parameter of the copy here method so if we have a look back at the documentation if i scroll downwards the second parameter allows you to specify a.
Number indicating what to do in cases such as the files already existing so i'm going to use number 16 respond with yes to all for any dialog box which is displayed.
So if i head back to the visual basic editor type in a comma at the end of this line and then enter the number 16 for the options parameter so when i run the subroutine again this.
Time we'll see that i don't get prompted the files simply get replaced one quick way to check that they are actually being replaced by the way as these are just empty text files there's.
Not much of a clue that that's actually worked so i could open up from the text files here type in something let's go for wiseowl and then save it and then close.
It so at the moment that has some text in it if i just run my subroutine again and then have a look back at the unzipped files folder reopen that text.
File we can see that it's been replaced with the new copy of that file now the code i've written here is a little longer than it really needs to be we can simplify this procedure quite a.
Lot actually the reason i wrote things in such a long-hand way is because well first of all it makes it easier for me to describe what's happening step by step.
But it also helps us to identify problems when we have things broken down in such a fashion but yeah we can tidy this up an awful lot let's first of all get rid of the.
Set destination folder section we don't need to have variables holding references to the folders we want to apply our methods to so i'm going to say sh.namespace this.
Workbook.path and unzipped files and then i'm going to apply the copy here method to the end of that line then just so i can break this line up into a couple of physical lines of code.
I'm going to type in a space and an underscore and then on the next line i'm going to get rid of set source folder equals replace that with a tab space.
And then at the end of that line refer to the items method then i can type in a comma and enter the number 16 so that i can click yes on any confirmation dialog box.
Required and then i can get rid of the destination folder dot copy here method from the following line that means that i no longer need these two variables dim destination folder or.
Source folder they can both disappear that's pretty neat and tidy compared to the previous version but we can go even a stage further when we have variables or classes that.
We're instantiating creating new instances of with the new keyword as well as being able to set the new instance explicitly in a line like this you can declare auto instancing.
Variables by including the new keyword in the variable declaration so i'm going to add the new keyword to the declaration of the variable and then i can get rid of the set sh.