Home » Blogs » Vishesh's blog

Unzip Files (using VBA)

Vishesh's picture
Submitted by Vishesh on 4 March, 2011 - 18:14

Here is the small piece of code that unzips file into a target folder. Just use the following code procedure with your own parameter values. 

Sub TestRun()
 
    'Change this as per your requirement

    Call UnZip("C:\Users\NC\Desktop\Vishesh\Test", "C:\Users\NC\Desktop\Vishesh\Test\TestZipFile.Zip")
 
End Sub
 
 
 
Sub UnZip(strTargetPath As String, Fname As Variant)
 
    Dim oApp As Object
 
    Dim FileNameFolder As Variant
 
 
 
    If Right(strTargetPath, 1) <> Application.PathSeparator Then
 
        strTargetPath = strTargetPath & Application.PathSeparator
 
    End If
 
 
 
    FileNameFolder = strTargetPath
 
 
 
    Set oApp = CreateObject("Shell.Application")
 
    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items
 
End Sub
»
YasserKhalil's picture

Can it be used with rar

Submitted by YasserKhalil on 5 May, 2015 - 17:05.

Can it be used with rar extension?
Can it be added to deal with that type of compression?

»

.txt extension

Submitted by MikeW on 16 April, 2013 - 17:04.

Hi, the Winzip file I have has a .txt extension rather than .Zip Therefore this code won't work. Do you know of a way around this? Thanks

»

Change the name of file

Submitted by zystof on 16 October, 2012 - 05:29.

Thanks for this it is a great help, I'm just wondering, what would you add to the line that is unzipping the file to give that file a different name?

»

Encrypted

Submitted by Anonymous on 14 February, 2012 - 18:39.

Thanks a lot. The code is nice and tiny and works perfect. I just wonder if I can use it with encrypted archives and how to paa a password in this case.

Thanks for your help,
Svitlana

»

Getting error in MS Access while using this piece of code

Submitted by Anonymous on 26 December, 2011 - 18:27.

I have developed a small tool which will download a zip from the site. I wanted to unzip the file and use the data. I found your code in net to unzip the zip files. But, unfortunately its not working for me. I am getting the error "91 Object Variable or With block variable is not set". Any help on this would highly appreciated.

PFB the code I am using in my tool:

Dim oApp As Object
Dim FileNameFolder As Variant

If Right(strTargetPath, 1) <> Application.PathSeparator Then
strTargetPath = strTargetPath & Application.PathSeparator
End If

FileNameFolder = strTargetPath

Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).Items

Set oApp = Nothing

»
Vishesh's picture

How and where are you calling

Submitted by Vishesh on 27 December, 2011 - 07:16.

How and where are you calling this function ?

»

Same issue

Submitted by Anonymous on 7 February, 2012 - 15:11.

I get the same error 91, "object variable or with block variable not set".

It occurs on the oApp.Namespace line. Does the .Namespace require a reference to be added? I'm running this in Excel 2010 64-bit on Windows 7 64-bit.

Hope you can help!

»

Resolved

Submitted by Anonymous on 7 February, 2012 - 15:18.

The issue I had (and likely the other poster had) is the destination folder does not exist.

Here is revised code that will create your folder if it does not exist:

Sub UnZip(strTargetPath As String, Fname As Variant)
Dim oApp As Object, FSOobj As Object
Dim FileNameFolder As Variant

If Right(strTargetPath, 1) <> Application.PathSeparator Then
strTargetPath = strTargetPath & Application.PathSeparator
End If

FileNameFolder = strTargetPath

'create destination folder if it does not exist
Set FSOobj = CreateObject("Scripting.FilesystemObject")
If FSOobj.FolderExists(FileNameFolder) = False Then
FSOobj.CreateFolder FileNameFolder
End If

Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

Set oApp = Nothing
Set FSOobj = Nothing
Set FileNameFolder = Nothing
End Sub

»

getting error "91 object variable or with block variable not

Submitted by Anonymous on 22 March, 2012 - 12:40.

Hi,

Am using your sample code for extracting the files from a zip file. Still am getting the error "91 object variable or with block variable not set" at oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items line

Appreciate your help for any solution on this issue.

»

getting error "91 object variable or with block variable not

Submitted by Anonymous on 12 June, 2012 - 13:46.

I had this same problem and fixed it by ensuring that both FileNameFolder and Fname are initialised as Variant.

Alternatively, try the following:
oApp.Namespace(CVar(FileNameFolder)).CopyHere oApp.Namespace(CVar(Fname)).items

»