Shell Command from Access VBA - Alert Access When Finished 4

[LogisticsNGR]

I am using the 'shell' command from within Access to run an external application (AMPL -- an optimization software).

How do I make it so that Access knows when AMPL is done running so that I can automatically import AMPL's solution file?

Any help is appreciated.

 

[EBGreen]

The shell function returns the process id of the spawned process. You could do a while loop that monitors for that pid.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[p27br]

Hi,

if you want the code to wait for the shell process to finish,
if you are using A2K paste this code into a vba module :

Option Compare Database
Option Explicit

Private Type STARTUPINFO
   cb As Long
   lpReserved As String
   lpDesktop As String
   lpTitle As String
   dwX As Long
   dwY As Long
   dwXSize As Long
   dwYSize As Long
   dwXCountChars As Long
   dwYCountChars As Long
   dwFillAttribute As Long
   dwFlags As Long
   wShowWindow As Integer
   cbReserved2 As Integer
   lpReserved2 As Long
   hStdInput As Long
   hStdOutput As Long
   hStdError As Long
End Type

Private Type PROCESS_INFORMATION
   hProcess As Long
   hThread As Long
   dwProcessID As Long
   dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal _
   hObject As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
Public Sub ExecCmd(cmdline$)
   Dim proc As PROCESS_INFORMATION
   Dim start As STARTUPINFO
   Dim ReturnValue As Integer

   ' Initialize the STARTUPINFO structure:
   start.cb = Len(start)

   ' Start the shelled application:
   ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
      NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

   ' Wait for the shelled application to finish:
   Do
      ReturnValue = WaitForSingleObject(proc.hProcess, 0)
      DoEvents
      Loop Until ReturnValue <> 258

   ReturnValue = CloseHandle(proc.hProcess)
End Sub

you can then call the shell like this :
ExecCmd ("c:\test.bat")

if you are using other verion of access, there are other versions of this code on these forums or at msdn

 

[LogisticsNGR]

Thanks to both TomThumb and p27. The latter's code worked perfectly.

 

[PHV]

Another way:
Set sh = CreateObject("WScript.Shell")
RC = sh.Run("Your command line here", 1, True)

Rc will contain the return code(ErrorLevel) of the command

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LogisticsNGR]

Thank you PH. It also worked perfectly and clearly is shorter. One question: How long would it wait for the called application to complete? Forever? Is there a way to set a time limit?

 

[PHV]

AFAIK, no time out.
Sometimes AMPL never ends ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LogisticsNGR]

It can run for hours depending on the size of the problem. not a huge deal for me since i will be the only one using access to call AMPL. but i was curious since VB has a SHELL command with a time parameter that allows you to state how long to wait on the application before moving. i do not know why i cannot use the VB shell command within access. Again, not a huge deal and the code you've supplied works well.

What is AFAIK?

 

[CajunCenturion]

AFAIK == As Far As I Know

In answer to your question, no, the Script Object does not provide for a timeout.

On the other hand, p27br's method can be modified to allow for a timeout.

Public Sub ShellAndWait(rStr_CmdLine As String, rInt_WindowStyle As Integer, rBol_WaitForTerm As Boolean, rLng_MilliSecWait As Long)

   Dim lLng_ProcessID      As Long
   Dim lLng_ProcessHandle  As Long
   
   lLng_ProcessID = Shell(rStr_CmdLine, rInt_WindowStyle)
   If (lLng_ProcessID > 0) Then
      lLng_ProcessHandle = OpenProcess(SYNCHRONIZE, False, lLng_ProcessID)
      If (lLng_ProcessHandle > 0) Then
         If (rBol_WaitForTerm = True) Then
            Do While WaitForSingleObject(lLng_ProcessHandle, rLng_MilliSecWait) = WAIT_TIMEOUT
               DoEvents
            Loop
         Else
            WaitForSingleObject lLng_ProcessHandle, INFINITE
         End If
         CloseHandle lLng_ProcessHandle
      End If
   End If

End Sub

All you need to do is add the additional parameters, including the 4th parameters which is the number of milliseconds that you'll wait for the process to complete. The millisecond value is a Long, so its value can extend to over 40 days, but the Windows kernel probably won't run that long. Anyway, inside the loop, you can control how long you want to wait, and with a few simple updates, how many times of that interval you want to wait.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[LogisticsNGR]

Excellent. Very helpful. Thank you.

 

[CajunCenturion]

I neglected to provide the values of the two additional constants required.

Private Const SYNCHRONIZE = &H100000
Private Const WAIT_TIMEOUT = &H102


Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein