Function to unzip a file to the same folder (AC2007)

[AOB]

Hi guys,

I'm trying to write a function to unzip a file (working off Ron de Bruin's code here)

The function should take the path of the zipped file as an argument and return the path of the unzipped file (extracting to the same folder)

Public Function UnZipFile(strFile As String) As String
 
  On Error GoTo ErrorHandler
 
  Dim objFSO As Object
  Dim objShellApp As Object
  Dim strUnzipped As String
 
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set objShellApp = CreateObject("Shell.Application")
 
  With objFSO
 
    objShellApp.Namespace(Replace(.GetAbsolutePathName(strFile), .GetFileName(strFile), "")).CopyHere _
    objShellApp.Namespace(.GetAbsolutePathName(strFile)).Items
 
  End With
 
Exit_UnZipFile:
 
  Set objFSO = Nothing
  Set objShellApp = Nothing
 
  Exit Function
 
ErrorHandler:
 
  Call LogError(Err.Number, Err.Description, "UnZipFile", "modImportFunctions")
 
  Resume Exit_UnZipFile
 
End Function

But the code has no effect (i.e., I'm still left with just the zipped file in the folder, not the unzipped contents?)

There's no error captured so I don't know why this doesn't work? strFile is a legitimate path to an existing .zip file which contains a zipped .xls file

I debugged and put a watch on...

objShellApp.Namespace(.GetAbsolutePathName(strFile)).Items

...but found that the .Count property was 0?

I've done several searches on how to unzip files using VBA and this seems to be the universally accepted method.

Can anybody help me figure out why it's not working for me?

Thanks

Al

 

[pr2-eugin]

It works for me ! Did you try a refresh on the Folder? I could not find the file there at first, did a refresh and it appeared to be there.

 

[AOB]

Thanks Paul

Nope, definitely not there - did a manual refresh in Windows Explorer, there is definitely no unzipped file available

 

[AOB]

Paul - not sure if this is the root cause but I appear to have PKZIP installed as the 'default' zip program?

 

[pr2-eugin]

I don't think so, since it calls for the Shell application (AKA windows). So it does not matter what is the default ZIP program. As a matter of fact, I have 7Z. Did you check the logger table for any errors? It suppresses the error without any prompt but is stored in the table.

 

[AOB]

Paul

Are you referring to my own logger table (populated by the LogError function)?

Or another logger somewhere in Access?

There is no call to my error handler and I'm watching the Err variable through debugging and it never changes from 0?

 

[pr2-eugin]

Yes I am talking about your Error Logger.

:
Set objShellApp = Nothing

Exit Function

ErrorHandler:

Call LogError(Err.Number, Err.Description, "UnZipFile", "modImportFunctions")

I was not sure what this LogError function does, but by the looks seems to Log Errors. So just thought you might have overlooked it.

 

[AOB]

Correct, it just writes the error details to a table - but it's never triggered here

That's why I debugged to see what properties...

objShellApp.Namespace(.GetAbsolutePathName(strFile)).Items

...took on?

And it's getting a .Count of 0 which suggests that it can't see any files in the archive? But when I manually open the .zip file through PKZIP I can see the compressed file there? And I can extract it manually no problem?

It's really frustrating me now because I've trawled the net looking for alternatives and I keep coming back to this .Namespace.CopyHere method but I can't get it to work :banghead:

 

[AOB]

Paul - did some tinkering and isolated a problem...

I ran the same code with a different .zip file and the contents extracted perfectly

So the problem is with the specific .zip file I'm trying to extract and not the code

Now the .zip file I'm trying to extract arrives via e-mail (prior code saves a copy locally and then this code should unzip the locally saved copy)

I noticed, when I check the attachment of the e-mail, that the filename looks like this :

Filename.xls.zip

Really infuriates me when people aren't careful with file extensions when saving copies but I'm wondering if this may be the source of the problem?

The e-mail is generated automatically from elsewhere so I have no control over their naming convention - this is what I get and I'm stuck with it. It does arrive as a .zip file - Windows recognises it as such and it shows with the PKZIP icon. If I double-click on it, it opens PKZIP as normal. And when I save the local copy, I rename it to a completely new name anyway (obviously, without that *.xls* piece in there) But I still can't extract that locally saved version.

Any suggestions as to how I can examine the received file to isolate why my code won't extract the contents? I can do it manually, no problem, it's only the code that falters?

Should I start a new thread?...

Any suggestions?

 

[pr2-eugin]

prior code saves a copy locally and then this code should unzip the locally saved copy

So does this not provide any renaming convention? As you know only the last extension will be recogonized by the System.

Example, I can have a file name Paul.txt.zip.avi.html.php The System will know it is a PHP file and try to assign the default open program's icon.

However the problem I guess is that the UNZIP code might recognize only the first extension; in your case XLS so it ignores to try and extract it.

Any suggestions as to how I can examine the received file to isolate why my code won't extract the contents? I can do it manually, no problem, it's only the code that falters?

In my head I have two opinions,

1. Check for the file name, get the number of times a "." will occur if it is more than once, then try replacing the . with an vbNullString and then append a .ZIP at the end of the file name before saving it to local drive.

2. A long winded process, get the file count in the folder, and use the call to extract, then do a folder count again, if the first count matches the second then the file has not been extracted. So you can "Raise an Error", thus logged into the Error log table.

Should I start a new thread?...

You most certainly can, I am sure other will have a much better idea. Good luck, and Kudos for figuring it out.

 

[AOB]

Thanks Paul

Sorry, yes, I rename the file when I save a local copy, I hoped that would overcome the problem. So even though the attachment is called "Filename.xls.zip", I save it locally as, say, "AOB.zip" anyway. And WIndows still recognises it as a .zip file and assigns it the appropriate icon. So not sure why the code fails to extract the contents?

Also, there's not much point in me writing a routine to loop through the file count just to log an error to the table - I know up front that there's a problem with these files, logging it ain't gonna fix it! These e-mails come in every day so it's going to be the same problem every day - not much good if I just get a logged error every day!

I'll mark this thread as resolved as I think the code is fine, it's the file that's the problem

Thanks for your help!

 

[vbaInet]

You're missing several bits of code. Here you go:

Public Function UnZipFile(strFile As String) As String
 
On Error GoTo ErrorHandler

    Dim objShellApp         As Shell
    Dim objFSO              As FileSystemObject
    Dim zippedGroup         As FolderItems
    Dim strNewFolderName    As String
    
    Set objFSO = New FileSystemObject
    Set objShellApp = New Shell
    
    ' Create a new folder - the name is derived from the name of the zipped folder _
      and created in the same parent folder as the zipped folder
    With objFSO
        strNewFolderName = .GetParentFolderName(strFile) & "\" & .GetBaseName(strFile)
        .CreateFolder strNewFolderName
'        ' Create the folder
'        MkDir strNewFolderName
    End With
    
    ' Get a collection of the files in the zipped folder
    Set zippedGroup = objShellApp.Namespace(strFile).Items
    
    ' Copy the items to the newly created folder
    objShellApp.Namespace(strNewFolderName).CopyHere zippedGroup

Exit_UnZipFile:
 
     Set objFSO = Nothing
     Set objShellApp = Nothing
    
     Exit Function
 
ErrorHandler:

'     Call LogError(Err.Number, Err.Description, "UnZipFile", "modImportFunctions")
    
     Resume Exit_UnZipFile
 
End Function

You'll need to add the following references:

Microsoft Scripting Runtime, scrrun.dll
Microsoft Shell Controls and Automation, shell32.dll

If you can't find it in the References list, browse for the dll, add it and tick it. You can always revert to late binding (i.e. without references) once you get it working.

 

[vbaInet]

Forgot to mention that you'll need to catch the error if the folder you're trying to create already exists.

Another solution is to use a zip utility like winzip, run it through command line with the correct switches and unzip it.

 

[AOB]

Hi vbaInet

Thanks for the effort, it is appeciated, however the reason I created the new thread was because we had already identified that the code wasn't the problem - my original code works perfectly fine with other .zip files?

Using your code, when I get to here :

' Get a collection of the files in the zipped folder
Set zippedGroup = objShellApp.Namespace(strFile).Items
 
' Copy the items to the newly created folder
objShellApp.Namespace(strNewFolderName).CopyHere zippedGroup

Once set, the zippedGroup object has no items (i.e. zippedGroup.Count = 0 according to the debugger) There is no error - it does get set to the contents of the folder; the object becomes a FolderItems-type object - but the code believes it to be an empty folder. Even though a manual check of the .zip file in question shows one compressed .xls file within it. Therefore, the .CopyHere command which follows, copies nothing into the new folder.

The problem I have is, how can I tell what is wrong with these particular .zip files so that i can either code the unzip in a different way or so that I have something tangible with which I can go out to the originators to explain how/why they are zipping their files wrong in the first place?

Using an alternative zip utility is not possible, PKZIP is the standard utility used throughout my company and IT won't change that for one function in one database

Any thoughts?

Thanks

Al

 

[vbaInet]

What that code is actually doing is not unzipping, it's just attempting to view and copy files from the zipped folder to another location.

I will try two things:

1. Rename the folder
2. Rename the ext, for example change it from .zip to .zipx. Then change it back.

I remember doing what you're doing, pulling zipped files from an FTP server and unzipping them. I used winzip's command line switches via VBA to unzip the files and I'm sure PKZIP has command line options too.

By the way, if you unzip the problem folder using PKZIP GUI or the Windows one, does it actually unzip? If it does, then you must unzip properly.