Macro to zip documents

Posted by Pierre on August 27, 2001 9:29 PM

Is is possible to zip document using a VBA excel macro?

I mean, I am currently performing many times these 2 operations daily
1. using the command of the type ActiveWorkbook.SaveAs FileFormat:=xlnormal etc (my Macro generates about 20 different files)
2. saving MANUALLY all the files with WINZIP.

I would like to do operation 2 programatically, so insert a code that call winzip and ask it to save the ActiveWorkbook.

Posted by Ivan F Moala on August 28, 2001 2:21 AM

Try zipping he files using shell command
eg.

Sub ZipFile()
Dim ZipPath As String
Dim ZipIt As String
Dim Source As String
Dim Dest As String

ZipPath = "C:\Program files\Winzip\"
Source = "C:\Final.xls"
Dest = "C:\tt.zip"
'Note spaces important
ZipIt = Shell(ZipPath & "Winzip32 -a " & Dest & " " & Source, vbNormalFocus)

End Sub

Change as required....probably need to loop through.

Ivan

Posted by Pierre on August 28, 2001 4:18 AM

It looks positive (it opens winzip) but it doesn't work:
1. I receive the information MsgBox "Multiple files were dropped and one or more is an archive. Add files to Archives?"
(it is probably bcs the line is not compatible to WINZIP version 8)
2. then, WinZip asks if I want to add the files, if I click yes
3. then it says I am adding 2 files and it wants to add the archive: C:/Temp, etc (it knows the path but not everything the destination file name)

If I input the file name by myself, then it works, but it is as painful as before and the macro does not help.

the line I coded looks pretty much the same than yours:
Shell(sZipPath & "WINZIP32" & " " & sdestination & " " & SSource, vbnormalfocus)
I had to add " " between winzip32 and the destination.
Can anyone help me more?
Thanks

Posted by Jerid on August 28, 2001 5:20 AM

Pierre, the line needs to be exactly like Ivan typed it. You missed the -a after WINZIP32

Jerid It looks positive (it opens winzip) but it doesn't work:

Posted by Pierre on August 28, 2001 9:31 PM

THANK YOU GUYS, it happened that I forgot a space between Winzip32 and -a, then I unsuccessfully tried to modify the code.
Thank you again,

Pierre, the line needs to be exactly like Ivan typed it. You missed the -a after WINZIP32 Jerid : It looks positive (it opens winzip) but it doesn't work:

Posted by Ivan F Moala on August 28, 2001 10:26 PM

Thanks Jerid

Jerid : It looks positive (it opens winzip) but it doesn't work: