Executing .BAT file from Access

[NoiCe]

I seem to be having problems here. Bare with me I don't use VBA. This is the steps that I took.

1. Created a Module called basAPI.


2. Created a Sub:

Sub shellwait()
    Shell ("C:\TEMP\Directory.bat")

3. Underneath it i created the function:

Public Function ShellWait(Pathname As String, Optional WindowStyle As Long) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
        .cb = Len(start)
        If Not IsMissing(WindowStyle) Then
            .dwFlags = STARTF_USESHOWWINDOW
            .wShowWindow = WindowStyle
        End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
            NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ret& = CloseHandle(proc.hProcess)
    ShellWait = ret
End Function

4. Under RunCode I am calling basAPI

It's coming up with errors that state Compile Error: Ambiguous name detected: ShellWait

[RuralGuy]

Get rid of that 1st Sub and then RunCode ShellWait and pass it the string you want "C:\TEMP\Directory.bat"

[NoiCe]

OK...the below is what I have as far as the code in basAPI Module. Within the Macro I have:

Runcode:
Function Name = ("C:\Green\Master List\Directory.bat")

I run it and it looks like nothing runs. I added a message box and the message box pops up almost immediately but I do not see that the batch file has ran.

Option Compare Database
'***************** Code Start ******************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Terry Kreft
Private Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&
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
    
Public Function ShellWait(Pathname As String, Optional WindowStyle As Long) As Long
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO
    Dim ret As Long
    ' Initialize the STARTUPINFO structure:
    With start
        .cb = Len(start)
        If Not IsMissing(WindowStyle) Then
            .dwFlags = STARTF_USESHOWWINDOW
            .wShowWindow = WindowStyle
        End If
    End With
    ' Start the shelled application:
    ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
            NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
    ' Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE)
    ret& = CloseHandle(proc.hProcess)
    ShellWait = ret
End Function
'***************** Code End ****************

[RuralGuy]

I do not see where this:

Runcode:
Function Name = ("C:\Green\Master List\Directory.bat")

runs the ShellWait() function.

[NoiCe]

It's not within VBA...it's actually in the Macro. I searched for RunCode and at the bottom where it says Function you get to click on the button "..." and it pulls up the builder. That's where I entered that in.

[RuralGuy]

How about just zipping up your db and attaching it and we'll look at it.

[NoiCe]

I will create a sample database tonight and upload it for you...Thanks again.

[NoiCe]

Hey Rural,

Sorry for not getting back to you sooner. Attached is a test db that i feel will provide an example of what i need to happen.

Basically I want to be able to hit the command button that will run all of my macros (which i did not include since a lot of the tables are part of an ODBC connect).

Part of this macro should be the ability for Access to call a Bat file that will produce dir.txt (directory of all tif files in a specific folder). The trick is for access to wait until the bat process is complete (cmd window is exited) and then upload the dir.txt file as the macro calls for it.

I uploaded a test folder containing example tif files along with the test db.

Please let me know if you have any questions. I have limited access with VBA so this is a tuffy for me. All my macros are part of the GUI and not VBA.

Thanks,
- J

[NoiCe]

Just wondering if anyone was able to look at this. Your help is greatly appreciated!