asked on

Unzip with Excel VBA is show

I have an automated process where at one point a zip file needs to be unzipped. I use the following code to do that. It works on my development laptop, fast - as it should.

On my client's PC it takes 5-10 mins for the files to appear in the folder. But, manual unzipping is instantaneous.

I'm wondering what could cause the 5-10 minutes?
Is it that the unzipping actually happens instantaneously, but it's taking a while to 'appear' in windows explorer? If so how can I test that it's there but not showing?

Here's my code

Sub UnzipTheCSVZip()

    'from Ron de Bruin
    'http://www.rondebruin.nl/win/s7/win002.htm (Example 3)

    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    
    Dim strZipFileName As String

    Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
                                        MultiSelect:=False)

                                        
    If Fname = False Then
        'Do nothing
    Else
        DefPath = "C:\Zip Reports\"  '<<< Change path
                
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If

        FileNameFolder = DefPath

        '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

    End If
End Sub

Open in new window

Bill Prew

Does the "You find the files here" message take all that time, or does it show up quickly?

~bp

hindersaliva

ASKER

The message shows up quickly. But looking in the folder the files are not there, apparently. But they 'appear' 5-10 minutes later. (I'm relating what my user tells me).

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

Thanks BP.
Followed up on info I found here (in the comments by the OP of that thread)
http://stackoverflow.com/questions/19809056/vba-script-to-unzip-files-its-just-creating-empty-folders

I created a zip file myself with the 3 csv files (they are not large BTW - < 2 Mb). And (lo and behold!) that delay does not happen. Which makes me think the scenario is similar to that in the link above. The zip file in question is one downloaded from a cloud enterprise application that my client uses. So it would appear that that zip file has some 'restriction' or flag that the one I created for testing does not have.

Still looking ....
thanks

Bill Prew

One thing you can check is the properties of the downloaded file that isn't working as fast. Right click on it in File Explorer and do Properties, then on the General tab see if it is "blocked" near the bottom. Windows does sometimes block files from the internet, but typically it would not allow you to unzip it, not just be slow.

I guess the other question is if the file that is downloading slowly was built with some odd ZIP program, etc.

You might also check if the computer is set to preserve zone information in downloaded files, I'm not on a domain, so I do this via gpedit.msc and note the setting below.

»bp

hindersaliva

ASKER

BP, if it's possible to programatically open the Zip file (and Extract All button) with VBA, my users are happy to click the Extract button in the next dialog (if that's the only thing they have to do).
So, would it be possible to programatically 'double click' a specific zip file, to only go up to the dialog box where we normally click 'Extract'?

ie. (1) Open and (2) Extract All

Thanks

hindersaliva

ASKER

Continued ..... so instead of

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

Open in new window

.... which copies the files to a folder, to only 'open' the zip file in the dialog window, so that the user can click 'Extract' manually.

Bill Prew

Yes, this should open the Zip file as if you had double clicked it in File Explorer.

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "C:\Temp\yourzip.zip"

Open in new window

»bp

hindersaliva

ASKER

Hi Bill, I tried this to try it out but it looks like I'm missing something.

Sub Do2()

    Dim WshShell As Object

    Set WshShell = WScript.CreateObject("WScript.Shell")
    WshShell.Run "C:\Reports\applications.zip"

End Sub

Open in new window

Thanks

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

Bill, sorry for the delay. I get this error.

I'm running Excel 2016 32-bit on a 64-bit PC.

hindersaliva

ASKER

Solved that error. It's because my path included directories with spaces. As here http://stackoverflow.com/questions/21267283/error-running-shell-object-commands-through-excel-vba

hindersaliva

ASKER

One last question on this.

Ok, the default unzip program opens (in my case Explorer).

Would it be possible to force the extract folder to a specific folder? And perhaps also suppress the 'show in folder' check box?

Bill Prew

Would it be possible to force the extract folder to a specific folder? And perhaps also suppress the 'show in folder' check box?

Unfortunately, no.

~bp

hindersaliva

ASKER

Ok. Thanks for your help Bill.