ShellAndWait Query

[DS]

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!

 

[Rick Rothstein \(MVP - VB\)]

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]

Many Thanks Rick, this has worked perfectly (as you knew it would!).

Much Appreciated,
DS

 

[Ron de Bruin]

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

 

[abcdefg]

Dear all,

I am new to VBA and have also encountered a problem with the ShellandWait
function on Ron DeBruin's site. It works perfectly, with the exception that I
get a pop up saying that a file exists every now and again. To save me having
to sit at my computer for hours, is there a way to automatically select yes?


This is the code I used:
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


I have tried inserting application.displayalerts = false/true and this does
not work.

I would greatly appreciate any help!

Thank you!

 

[Dave Peterson]

I didn't look at Ron's site and I don't see anything in the code you posted that
would cause that warning...

But maybe you could delete the file that's causing the error before you use the
ShellAndWait procedure:

On Error Resume Next
Kill "C:\somepath\somefilenamehere.extensionhere"