[AccessD] Shelling to a batch file

Fri Mar 5 16:21:31 CST 2010

I use something very similar as well:

It's similar to Max's version but calls in CreateProcessA in Kernel32 to Shell the command, bat or executable.  I've used it a great deal with things like Robocopy:

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

Public Declare Function CloseHandle Lib "kernel32" (ByVal hFile 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, lpStartupInfor As STARTUPINFO, lProcessInformation As _
    PROCESS_INFORMATION) As Long

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 Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Sub ShellWait(pathname As String, Optional WindowStyle As Long)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long

    With start
        .cb = Len(start)
        If Not IsMissing(WindowStyle) Then
            .dwFlags = STARTF_USESHOWWINDOW
            .wShowWindow = WindowStyle
        End If
    End With
    ret& = CreateProcessA(0&, pathname, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    CloseHandle proc.hProcess
End Sub

This allows me to specify maximized, restored or hidden windows for the process though I nearly always run it hidden.

Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com

> From: max.wanadoo at gmail.com
> To: accessd at databaseadvisors.com
> Date: Fri, 5 Mar 2010 22:02:41 +0000
> Subject: Re: [AccessD] Shelling to a batch file
> 
> Hmm, I have a similar one Jim, code below.
> 
> Is there any value in one over the other do you think?
> 
> Max
> 
> Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long)
> As Long
> Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess
> As Long, ByVal bInheritHandle As Long, ByVal dwProcessId 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 = -1&
> 
> Public Sub pfRunUntilFinished(ByVal strApplication As String)
> ' EG: Call
> pfRunUntilFinished("C:\AbyssWebServer\htdocs\MasterIndexCopy.bat")
> Dim lProcID As Long
> Dim hProc As Long
> ' Start the App
> lProcID = Shell("CMD /C " & strApplication, vbHide)
> DoEvents
> ' Wait for the App
> hProc = OpenProcess(SYNCHRONIZE, 0, lProcID)
> If hProc <> 0 Then
> WaitForSingleObject hProc, INFINITE
> CloseHandle hProc
> End If
> exithere:
> Exit Sub
> errhandler:
> MsgBox "pfRunUntilFinished - Errors for " & Err.Number & Err.Description
> Resume exithere
> End Sub
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Friday, March 05, 2010 9:50 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Shelling to a batch file
> 
> 
> and for those folks that want to pause until the Shelled process has
> finished:
> 
> Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess
> As Long, lpExitCode As Long) As LongPrivate Declare Function OpenProcess Lib
> "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long,
> ByVal dwProcessId As Long) As Long
> 
> ' Used for wait check.
> Const STILL_ACTIVE = &H103
> Const PROCESS_QUERY_INFORMATION = &H400
> 
> Public Sub WaitWhileRunning(lngHWnd As Long)
> 
> Dim lngExitCode As Long
> Dim lnghProcess As Long
> 
> 10 lngExitCode = STILL_ACTIVE
> 20 lnghProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lngHWnd)
> 
> 30 If lnghProcess > 0 Then
> 40 Do While lngExitCode = STILL_ACTIVE
> 50 Call GetExitCodeProcess(lnghProcess, lngExitCode)
> 60 DoEvents
> 70 Loop
> 80 End If
> 
> End Sub 
> 
> Call it like this:
> 
> ' Execute
> 170 lngHWnd = Shell(strFTPScriptFile, vbHide)
> 180 WaitWhileRunning (lngHWnd)
> 
> Jim.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
> Sent: Friday, March 05, 2010 4:28 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Shelling to a batch file
> 
> Max,
> 
> I use the SHELL command quite a bit to fire-up .bat and .exe files from
> Access.
> 
> Below is a snippet of VBA code which you may find useful.
> 
> I am not sure if this will help you or not, but I thought that I would share
> what I have.
> 
> Please let me know if you have any questions.
> 
> Thanks,
> Brad Marks 
> 
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~
> 
> Const Four_Quotes As String = """"
> 
> Dim Var_String_For_Shell_Command
> 
> Dim Var_Program_To_Be_Initiated
> 
> Dim Var_Parm_Passed_To_Initiated_Program
> 
> 
> Var_Program_To_Be_Initiated = "C:\Documents and Settings\ABC\My
> Documents\InitXcel.bat"
> 
> Var_Parm_Passed_To_Initiated_Program = "TEST-ABC"
> 
> 
> Var_String_For_Shell_Command = _
> Four_Quotes _
> & Var_Program_To_Be_Initiated _
> & Four_Quotes _
> & " " _
> & Four_Quotes _
> & Var_Parm_Passed_To_Initiated_Program _ & Four_Quotes
> 
> MsgBox "Var_String_For_Shell_Command = " & vbLf & vbLf &
> Var_String_For_Shell_Command
> 
> Shell Var_String_For_Shell_Command
> 
> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> ~~~~~~~~~~~~~~
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
> Sent: Thursday, March 04, 2010 3:36 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Shelling to a batch file
> 
> Hi All,
> 
> I am struggling to get the SHELL command to run a batch file from Access.
> 
> Any clues or tips?
> 
> The batch file in turn will run and executable in the same folder as the
> currentproject.path BUT this exe file is NOT installed so it is necessary to
> ensure that I first move to that folder and then run the batch.bat file.
> 
> Eg:
> g\_MyTest is my currentproject.path within Access In there I have blat.exe
> which is not installed and which I do not want to install - so nothing in
> the Registry.
> I also have blat.bat which I have created from Access.
> I want to run the batch file which in turn will invoke the blat.exe and pass
> it parameters.
> 
> The batch file runs fine if manually invoked. This is the last bit of
> running Blat from within Access. 
> I will then post the lot to the List.
> 
> Thanks
> 
> Max
> 
> This is where I am at:
> 
> Private Sub sShell(sFile)
> ' sfile will be "blat.bat"
> const conQuote as string = """"
> Dim sPath As String
> ' move into the current folder because we havn't installed Blat.exe
> sPath = conQuote & " CD /D " & CurrentProject.Path & conQuote
> Debug.Print sPath
> ShellExecute Application.hWndAccessApp, "Open", sPath, "", "",
> vbNormalFocus
> 
> ' now run the batch file
> 'sFile = conQuote & sFile & conQuote
> Debug.Print sFile
> ShellExecute Application.hWndAccessApp, "Open", sFile, "", "",
> vbNormalFocus End Sub
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> This message has been scanned for viruses and dangerous content by
> MailScanner, and is believed to be clean.
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

_________________________________________________________________
Check your Hotmail from your phone. 
http://go.microsoft.com/?linkid=9712957