Application.Wait

  • Thread starter Thread starter UB
  • Start date Start date
U

UB

I need this help in VBA coding.

I'm currently using Application.wait Now + TimeValue("00:00:10") for
the period during which the code is read into another software and
'processed'. Most instances - this would be too short a period. The
time requried to process in the otehr sopftware would be entirely
dependent on the data size.

Can you help me with certain code, which will keep the other software
open till the processing is complete and then quits automatically -
without hard coded TimeValue.
 
I need this help in VBA coding.

I'm currently using Application.wait Now + TimeValue("00:00:10")  for
the period during which the code is read into another software and
'processed'. Most instances - this would be too short a period. The
time requried to process in the otehr sopftware would be entirely
dependent on the data size.

Can you help me with certain code, which will keep the other software
open till the processing is complete and then quits automatically -
without hard coded TimeValue.
Click to expand...

One possibility is to try to use VBScript's Run command (which can
call external programs synchronously). In your VBA project insert a
reference (via Tools/References) to the Windows Script Host Object
Model. Then something along the following might work:

Sub test()
Dim WShell As New WshShell
WShell.Run "Calc", WaitOnReturn:=True
MsgBox ("Done!")
End Sub

'************************************************
Note that when you run the above that the message box doesn't appear
until *after* you close calc.

Hope that helps.
 
UB was thinking very hard :
I need this help in VBA coding.

I'm currently using Application.wait Now + TimeValue("00:00:10") for
the period during which the code is read into another software and
'processed'. Most instances - this would be too short a period. The
time requried to process in the otehr sopftware would be entirely
dependent on the data size.

Can you help me with certain code, which will keep the other software
open till the processing is complete and then quits automatically -
without hard coded TimeValue.
Click to expand...

To add to John's excellent example, you can also use VB's 'Shell()'
feature with no external dependancy. (Some corporate network
ActiveDirectory GroupPolicies disable scripting libraries on local
workstations, and so John's suggestion will not work!) The Shell()
function can be used along with Windows APIs as in this example...

http://www.appspro.com/Downloads/ShellAndWait.zip
 
Back
Top