iopcj


WindowsXP can open and create a zipped file.

How to zip and unzip a file in VBA

Thanks for ur help.





Re: How to create a .zip file with VBA in WindowsXP without any third party software?

sjoo


Hello, iopcj.

I have a vba code that zips active workbook.

but I don't know how to unzip it. the code is not my work.

the code uses Windows Shell that is the core to work.

HTH.

--------------------------------------------------------------------------

Sub zip_activeworkbook()
Dim strDate As String, DefPath As String
Dim FileNameZip, FileNameXls
Dim oApp As Object

If ActiveWorkbook Is Nothing Then Exit Sub
DefPath = ActiveWorkbook.Path
If Len(DefPath) = 0 Then
msgbox "Plz Save activeworkbook before zipping" & Space(12), vbInformation, "zipping"
Exit Sub
End If

If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Create date/time string and the temporary xls and zip file name
strDate = Format(Now, " dd-mmm-yy h-mm-ss")
FileNameZip = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".zip"
FileNameXls = DefPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & strDate & ".xls"

If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

'Make copy of the activeworkbook
ActiveWorkbook.SaveCopyAs FileNameXls

'Create empty Zip File
newzip (FileNameZip)

'Copy the file in the compressed folder
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameZip).CopyHere FileNameXls

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(FileNameZip).items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0

'Delete the temporary xls file
Kill FileNameXls

msgbox "completed zipped : " & vbNewLine & FileNameZip, vbInformation, "zipping"

Else
msgbox "FileNameZip or/and FileNameXls exist", vbInformation, "zipping"

End If
End Sub

Private Sub newzip(sPath)
'Create empty Zip File
'Changed by keepITcool Dec-12-2005
If Len(Dir(sPath)) > 0 Then Kill sPath
Open sPath For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
End Sub

---------------------------------------------






Re: How to create a .zip file with VBA in WindowsXP without any third party software?

iopcj

Cool!

Great!

It's what I want!

Does anyone else find another way







Re: How to create a .zip file with VBA in WindowsXP without any third party software?

sjoo

Hi iopcj,

I've just found where I refer it from.

plz visit the following link:

Zip file or files with the default Windows XP zip program (VBA)

It will solve out your problem.

Bye~





Re: How to create a .zip file with VBA in WindowsXP without any third party software?

Bruce Scherzinger

This does not seem to work with Access 2003. Is there some object library I need to include to get it to work I get a run time error 91 when I try to reference the zip file object using the CopyHere attribute (method). I checked the installed libraries and the default appears to be VB6 as was stated somewhere else in here as being the necessary version.

Thanks in advance.