VBA Excel How to get the best compression ratio from winzip?

[knifey]

Hi,
I have some VBA code which uses winzip to create a temp copy of the current excel workbook, then save it to a zip file.
My problem is that it only acheives around a 34% compression ratio (from 482k to 330k approx). Does anyone know how to get the maximum possible compression ratio out of winzip using vba code? The speed of opening these files is not important.
I'm using windows 2000, excel 2000 and winzip 7.
Thanks,
Knifey

 

[HughLerwill]

It is a long time since I used Winzip for this but I seem to remember you have the option to use/ not use the Winzip Command line utility; I believe using the latter gives more control over options like compression. It may be good to post the code you are using so that others can advise.

I much prefer to distribute and Shell to Zip.exe and UnZip.exe which are in the public domain at

http://www.info-zip.org.

 

[JerryKlmns]

Back in time Remou, showed us a zipping technic on thread705-1269176, using the default Windows XP zip program (VBA).

I don't know if you could get better rate, nor have I test it. Of course he might have some extra tips if he was asked

 

[knifey]

Hi Hugh,
Here is the code (found on the net). I must admit I don't understand all of it.
My company will only use utilities installed by our pc depatment. So I'm afraid zip.exe is out of the question.
Any suggestions on how to make this file smaller would be much appreciated (via winzip or any other means).
Thanks,
Knifey

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

Sub Zip_ActiveWorkbook()
Dim PathWinZip As String, FileNameZip As String, FileNameXls As String
Dim ShellStr As String, strDate As String

PathWinZip = "C:\program files\winzip\"
'This will check if this is the path where WinZip is installed.
If Dir(PathWinZip & "winzip32.exe") = "" Then
MsgBox "Please find your copy of winzip32.exe and try again"
Exit Sub
End If

' Build the date/Time string
strDate = format(Now, "dd-mm-yy h-mm-ss")

' Build the path and name for the zip file
FileNameZip = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & " " & strDate & ".zip"

' Build the path and name for the xls file
FileNameXls = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4) & " " & strDate & ".xls"

' Use SaveCopyAs to save the file with a Date/Time stamp
ActiveWorkbook.SaveCopyAs Filename:=FileNameXls

'Zip the file
ShellStr = PathWinZip & "Winzip32 -min -a" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

'Delete the file that you saved with SaveCopyAs
Kill FileNameXls

MsgBox "The macro is ready"
End Sub

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub

 

[knifey]

Hi Jerry,
The code refered to in your thread is for access vba, I'm working with Excel. Also, I don't have windows xp installed. We are still running windows 2000 professional.
Thanks anyway,
Knifey

 

[HughLerwill]

A google gave me

http://www.memecode.com/docs/winzip.html

which lists the Winzip command line parameters; checkout the -ex, -en, -ef, -es, and -e0 options.

so you could try eg;

ShellStr = PathWinZip & "Winzip32 -min -a -ex" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)

Details for the WinZip Command line Add-in at

http://www.winzip.com/prodpagecl.htm

 

[knifey]

Thanks for that Hugh,
I've tested this but the -ex parm dosn't give a better compression ratio. I think I'm stuck with this one if my company won't even upgrade to winzip pro. Not sure if the command line add-in is even installed (and if it's not they won't allow it!)
Thanks for the help everyone,
Knifey