Hi DS

Note :
1) Don't forget to copy the Functions in a normal module.
2) Check out also the Unzip web page if you need examples for unzip a zip file.
3) Look on this page for all command line parameters that WinZip support.

http://www.rondebruin.nl/zip.htm#Functions

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DS" wrote in message ...
Many Thanks Rick, this has worked perfectly (as you knew it would!).

Much Appreciated,
DS

"Rick Rothstein (MVP - VB)" wrote:

I'm not familiar with the technique Ron used, so I can't help you debug it.
However, I have a different method for you to try and see if your code works
with it instead.

This is a method is different than the one from Ron that you are attempting
to use (it doesn't use a loop to perform its waiting operation) and is based
on code I have posted previously to the compiled VB newsgroups over the
years. The call statement from your own code to my ShellAndWait subroutine
is the same as for Ron's subroutine, so the only thing you will need to do
to make it work is to replace (comment out initially in case you want to go
back to it) all of Ron's ShellAndWait code and replace it with the code
below. If you hadn't already done so for Ron's routine, I would suggest
placing my code into a Module (Insert/Module from VBE's menu bar). As I said
before, call my ShellAndWait subroutine from within your own macro using the
exact **same** statement as you used to call Ron's version of ShellAndWait
(that is, you do **not** have to change your macro code at all to use the
code below).

Rick

'********** START OF MODULE CODE **********
Private Declare Function OpenProcess _
Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function CloseHandle _
Lib "kernel32" _
(ByVal hObject As Long) As Long
Private Declare Function WaitForSingleObject _
Lib "kernel32" _
(ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long

Private Const SYNCHRONIZE = &H100000
Private Const INFINITE = &HFFFF

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim PID As Long
Dim hProcess As Long
If IsMissing(WindowState) Then WindowState = vbNormalFocus
PID = Shell(PathName, WindowState)
If PID = 0 Then
' Handle Error, Shell Didn't Work
MsgBox "Error executing Shell command!"
Else
hProcess = OpenProcess(SYNCHRONIZE, True, PID)
WaitForSingleObject hProcess, INFINITE
CloseHandle hProcess
End If
End Sub
'********** END OF MODULE CODE **********



"DS" wrote in message
...
Afternoon all,

I'm hitting a slight problem trying to get the ShellAndWait command
running,
using the example on Ron DeBruin's site at:
http://www.rondebruin.nl/zip.htm.
I've simply copied & pasted the code relating to "Zip The ActiveWorkbook"
as
a starting point.

Specifically, I'm getting a "Sub Or Function Not Defined" error on the
line
ShellAndWait ShellStr, vbHide

I'm wondering whether a specific required reference is not active, or
whether this function isn't available in this Excel version (2k).
Replacing
ShellAndWait with Shell works ok, but I don't want VBA to continue running
until the zip operation is complete. Yes, I know I could simply insert an
Application.Wait, but this is gonna niggle at me until I know what's going
on!

Any ideas appreciated, cheers!