Unzip folder with files to the chosen location

Question

Team, I am working upon extract the zip file from VBA code but getting error, here is my code:

Sub Un_Zip_File()
Dim flname As String
Call PathCall
flname = Dir(impathn & "Transactions*.zip")
Call PathCall
Call UnZip_File(impathn, flname)
End Sub

Sub UnZip_File(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

'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

When I am running Un_zip_file macro, I am getting error:

Object variables or with block variable not set

after debug moving on

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(fname).Items

Either oApp, oApp.Namespace(FileNameFolder) or oApp.Namespace(fname) may be returning an error. Maybe you should test them before using them? — Vincent G, Mar 01 '16 at 08:08
Just going through your code - in the first procedure you use `flname = Dir(impathn & "Transactions*.zip")` which returns an empty path if the folder doesn't exist, then in your second procedure you assign the path used above to `FileNameFolder` and then check if it exists - it has to exist otherwise flname will fail. — Darren Bartrup-Cook, Mar 01 '16 at 12:09
@Dharmendra, you need to add second brackets: `oApp.Namespace((FileNameFolder)).CopyHere oApp.Namespace((fname)).Items` — Alekzander, Nov 24 '17 at 10:55
Ready-to-go _Zip_ and _UnZip_ functions in my project [VBA.Compress](https://github.com/GustavBrock/VBA.Compress). — Gustav, Dec 03 '20 at 17:50

score 9 · Answer 1 · answered Mar 01 '16 at 08:13

Here is another example how to unzip a file.
the macro unzip the zip file in a fixed folder"C:\test\"

Sub Unzip()
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String

    Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=False)
    If Fname = False Then
        'Do nothing
    Else
        'Destination folder
        DefPath = "C:\test\"    ' Change to your path / variable
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If

        FileNameFolder = DefPath

        '        'Delete all the files in the folder DefPath first if you want
        '        On Error Resume Next
        '        Kill DefPath & "*.*"
        '        On Error GoTo 0

        'Extract the files into the Destination folder
        Set oApp = CreateObject("Shell.Application")
        oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

        MsgBox "You find the files here: " & FileNameFolder

        On Error Resume Next
        Set FSO = CreateObject("scripting.filesystemobject")
        FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
End Sub

score 4 · Answer 2 · answered Dec 02 '18 at 00:01

4

Found elsewhere on the web and thought it might help here...

Sub UnzipAFile(zippedFileFullName As Variant, unzipToPath As Variant)

Dim ShellApp As Object

'Copy the files & folders from the zip into a folder
Set ShellApp = CreateObject("Shell.Application")
On Error Resume Next
ShellApp.Namespace(unzipToPath).CopyHere ShellApp.Namespace(zippedFileFullName).Items
On Error GoTo 0
End Sub

answered Dec 02 '18 at 00:01

Stickman68

41
1

Welcome to Stack Overflow, please review: https://stackoverflow.com/help/how-to-answer – Daniel Dec 02 '18 at 04:33

score 1 · Answer 3 · answered Dec 11 '19 at 14:00

1

I had same error "Object variables or with block variable not set".

Solved it by adding reference to "Microsoft Shell Controls & Automation" - Shell32.dll. Then define & use the Shell object in this order.

Do not skip any of these steps. I have also posted full code in this page.

Dim wShApp As Shell

Set wShApp = CreateObject("Shell.Application")
Set objZipItems = wShApp.Namespace(zipFileName).items  

wShApp.Namespace(unZipFolderName).CopyHere objZipItems

answered Dec 11 '19 at 14:00

OfficeTricks.Com

108
6

Also make sure the folder you are copying INTO exists. If it does NOT exist, you will get the "object variables or with block variable not set" error on the ```wShApp.Namespace(unZipFolderName).CopyHere``` line. – Amy Mar 16 '23 at 17:38

score 1 · Answer 4 · edited Dec 04 '20 at 09:52

1

To avoid the message error:

1 - change "/" per "\"

unzipToPath= Replace(unzipToPath, "/", "\\")

zippedFileFullName= Replace(zippedFileFullName, "/", "\\")

2 - Use double (( to the parameters as below:

ShellApp.Namespace((unzipToPath)).CopyHere
ShellApp.Namespace((zippedFileFullName)).Items

edited Dec 04 '20 at 09:52

Dr. Mantis Tobbogan

540
8
20

answered Dec 03 '20 at 16:38

Valdemar

11
1

score 0 · Answer 5 · answered Feb 28 '18 at 01:34

I had exactly the same problem, but in MS Word, trying to extract files from a .zip folder. After a lot of experimentation and testing I discovered that the late-binding objects were not initializing correctly, and when i tested them with the TypeName function were typically "nothing".

I tested my code in both Windows 10 and on an old Windows XP machine, with the same results. All my testing was in Excel 2007 and Excel 2016.

Changing the code from late-binding to early-binding resolved the problem.

Late-binding uses the CreateObject function to initialize the objects in the Shell.Application library. Early-binding requires setting a reference to the "Microsoft Shell Controls and Automation" library in your project.

To set the reference, do this: In the VBA IDE, use the Tools menu to open the References dialog. Scroll through the list of available references until you find the "Microsoft Shell Controls and Automation" entry, and then click the checkbox to select that library, thus: The VBA References dialog, showing the "Microsoft Shell Controls and Automation" library after adding it to your project.

Unzip folder with files to the chosen location

5 Answers5

Linked