Invoke Command Line (Shell Method with Winzip)

[ABAX via AccessMonster.com]

First - thanks for taking the time to read. Here is my situation. I am
trying to call WinZip from my VBA module. I have the macro creating a
spreadsheet and want RunCode macro to simply take the XLS file and turn to
Zip. I have WinZip Pro and the command line add-in. I have tried it from
the run command line and it works. What's the deal? After looking below -
you will probably realize that I am relatively new to VBA. Your help is
greatly appreciated.

Two methods I have tried:

1)Function winZipit(ByVal source As String, ByVal target As String, ByVal zip
As Boolean)
zipIT = App.Path & "wzzip -a"
unzipIT = App.Path & "wzunzip -e "

If zip = True Then
Shell (zipIT & target & source)
Else: Shell (unzipIT & target & source)
End If
End Function


2) Public Function ZipFile()

Shell ("C:\Program Files\WinZip\WZZIP.EXE -a" & "C:\Test.zip" & "C:\Test.xls")


End Function

 

[chris.nebinger]

I've not used the command line add-in, but I suspect your problem is
one of spaces.

Your line: Shell (zipIT & target & source)

Will get transferred to the command window as:

C:\Winzip\wzzip -a c:\test.zipc:\test.xls

If wzzip is expecting:

C:\winzip\wzzip -a c:\test.zip c:\test.xls

then you need to add spaces. Also, if there are any spaces in the name
of the target or source (which is common with My Documents, then they
need quotes:

C:\winzip\wzzip -a "C:\Documents and Settings\User\My
Documents\Target.Zip" "C:\Documents and Settings\User\My
Documents\Source.xls"

Which would be fixed in your code as:


'Air Code coming---->
Function Zip (Target as String, Source as String)
Dim strShell as String
strShell = "C:\Winzip\wzzip -a"
'chr(32) = space
'chr(34) = "
strShell = strShell & chr(32) & chr(34) & target & chr(34) & chr(32) &
chr(34) & source & chr(34)
Shell strShell
End Function


Chris Nebinger