Using VBA Shell cmd to return a variable

[Liam1]

Hello,

I am trying to run a batch file from Access. I'm not very good at VBA, but what I would like to do is:

Return a code to my Access database when the application closes.
e.g
Shell(File.Bat)
*BTEQ Opens*
*do something for 5 minutes*
*BTEQ Closes
now my bit:
a variable is populated/created upon the called application's closure, that will allow me to trigger another function.

(The only variable it seems to return is on OPENING BTEQ)

Is this even possible?

Thanks in advance,

Liam1

 

[WILLIEWANKA]

You can check to see if the process executed by your shell command has finished with some code.

This is from Microsoft.....

http://support.microsoft.com/default.aspx?scid=kb;en-us;178116&Product=vba

Create a module and type the following lines in the Declarations section:

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&

Type the following two procedures in the module:

    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

Then you would call it from your form, module or where using this code

      Sub Testing()
         ExecCmd

"path & file as string"

         MsgBox "Process Finished" 'This runs after the process has finished
      End Sub

Check thread705-55856 for more info, or search on "wait shell"

Let me know if you need some more help.


Bill

 

[Liam1]

Mr WillieWanka,

This has worked a treat, and is doing exactly what I want it to do 1st time, no extra coding necessary!!!!

Thanks!

Liam1.

 

[WILLIEWANKA]

Glad I could be of assistance


Cheers,
Bill