Unzip files using windows zip in Access VBA 4

[dssjon]

I need to know if it is possible to unzip files using VBA in Access. I am retrieving a file location from a table and I need to unzip the files before I move them into another directory. So far I have found out how to call the winzip application, but we do not use winzip in my office so I need to use the built in compression in windows. Any help is appreciated!

 

[Remou]

I found that the zip version of this:

http://www.rondebruin.nl/files/windowsxpunzip.txt

Could be modified for Access, I imagine the same is true for the unzip version.

 

[dssjon]

Thanks,

I will try to see if I can make it work. Any help on doing that is much appreciated.

 

[AccessGuruCarl]

Download a free version of pkunzip or UnRar

The free version of Un-Rar does not except .zip or vice-versa pkunzip does not accept .rar


Now here is the code....

 Dim strShortPath
 Dim strRunCmd As String
   For i = 1 To gFileCnt
     strShortPath = GetShortName(App.Path)
'Move into the folder where pkunzip & unrar reside.
     ChDir (strShortPath)

       If fGetExtension(gFiles(i) = "zip" Then
          strRunCmd = "Pkunzip.exe -o " & gblQuoteSym & str_ED & gblQuoteSym
       Else
          strRunCmd = "unrar.exe e -o+ " & gFiles(i)
       End If

    Call ShellWait(strRunCmd, 1)
    Next i

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[dssjon]

Thanks,

but I still need to use the built in windows compression with Access VBA. I do not have rights to install third party software so you can see the bind I am in. :\

 

[AccessGuruCarl]

Then you have to go with Remou post...

And you have to hope all the users are running WinXP, anything less and windows didn't offer a compress/decompress utilty.

Let us know if you any problems implementing the code from Remou post!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Remou]

Did you get this sorted to suit you? It is basically a case of copying from one folder to another.

'[URL unfurl="true"]http://www.rondebruin.nl/files/windowsxpunzip.txt[/URL]
Sub Unzip1()
    Dim FSO As Object
    Dim oApp As Object
    Dim fname
    Dim FileNameFolder
    Dim DefPath As String
    Dim strDate As String

    fname = CurrentProject.path & "\Tek-Tips.zip"
    
    If fname = False Then
        'do nothing
    Else
    'Set default path to current database folder
        DefPath = CurrentProject.path
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If

        strDate = Format(Now, " dd-mm-yy h-mm-ss")
        FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"

        'Create normal folder
        MkDir FileNameFolder

        Set oApp = CreateObject("Shell.Application")
        'Copy the files in the newly created folder
        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

        Set oApp = Nothing
        Set FSO = Nothing
    End If
End Sub

 

[dssjon]

Thanks Remou,

I am getting the following error with your code:

runtime error 91:

object variable or with block variable not set

at

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


I only modified the code with a *.zip instead of tek-tips.zip as shown in the last post. Does this have something to do with NameSpace?

 

[Remou]

*.zip would require different code. You can use any existing zip file name.

 

[AccessGuruCarl]

Set a reference to Microsoft Scripting Runtime.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[Remou]

AccessGuruCarl: the code uses late binding.

 

[dssjon]

Thanks again both of you,

Remou you are starting me off in the right direction. Much appreciated. I am going to see if I can call Unzip for each file in a listbox that is linked to a table with directories and file names.

 

[dssjon]

Hello again,

I was wondering if I can replace the file.zip with a reference to a string value in a listbox in access. The field values have file names. so instead of

fname = CurrentProject.path & "\Tek-Tips.zip"

I am trying:

Dim ctlList As Control, varItem As Variant
Set ctlList = Forms!FileProcessor!List0
fname = CurrentProject.Path & Me.List0.ItemData(varItem)

Does anyone know if this can work? The rest of the code is the same as last posts. The item names in my listbox are File.zip and Test.zip