asked on

VBA Batch File Not Executing On Shell

I am using VBA to generate batch files for execution. The BAT files are creating properly via, VBA however, when I try to execute the file using the Shell command , nothing happens!

I can navigate manually to the file, double click and it works perfectly. Any suggestions?

Dim db As Database
    Dim rs As Recordset
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryGroups")
    
    rs.MoveFirst
    Do While Not rs.EOF
    
    strGroup = rs!HRGroup
    
        
    Dim AppVer
    Dim fs, A As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    strFile = "D:\Documents and Settings\chriramw\My Documents\Downloads\Security\HRGrpMembers.bat"
    Set A = fs.CreateTextFile(strFile, True)
    
    
    A.writeline "showmbrs " & strGroup & " > HRGroupMembers.txt"
    A.Close
    Set fs = Nothing
    Set A = Nothing
    
    lngHWnd = Shell(strFile)
    WaitWhileRunning (lngHWnd)
    rs.MoveNext
    Loop
    
    rs.Close
    Set db = Nothing
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rsaphier

ASKER

Will either of the two suggestions still afford me the ability to wait until the bat file has closed before proceeding with the next steps of the routine? As it is coded now it executes a sub (code below) which determines when the bat file has completed processing.

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Const STILL_ACTIVE = &H103
Const PROCESS_QUERY_INFORMATION = &H400



Public Sub WaitWhileRunning(lngHWnd As Long)
        
    Dim lngExitCode As Long
    Dim lnghProcess As Long
    
    lngExitCode = STILL_ACTIVE
    lnghProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lngHWnd)
    If lnghProcess > 0 Then
        Do While lngExitCode = STILL_ACTIVE
        Call GetExitCodeProcess(lnghProcess, lngExitCode)
        DoEvents
        Loop
    End If

End Sub

Open in new window

rsaphier

ASKER

boag2000:
Nothing happens ... the file is not being executed. If it where I would know even if the DOS window were closing too fast for me to see as it would generate a text file as part of the process.

rsaphier

ASKER

Also, I forget to mention that the contents of the bat file are generated as part of the intial code posted.

Code
A.writeline "showmbrs " & strGroup & " > HRGroupMembers.txt"

Generates the following content of the bat file.
showmbrs BHI-MASTER\BHBSS_SC_Groups_Admin > HRGrpMembers.txt

rockiroads

Jeff, I tried running a batch script (tested by the script producing logging) and it didnt run in Shell. I tried prefixing it with cmd but alas that didnt work work.

rsaphier, if you just have one line to run then you do not need a batch script, instead use cmd /c

eg

Dim sCommand as string

sCommand = "showmbrs " & strGroup & " > HRGroupMembers.txt"

Shell "cmd /c " & sCommand

Now in order to add wait, either use what you did (which I have not seen b4) or use ShellAndWait, plenty of different examples around eg http://www.cpearson.com/excel/ShellAndWait.aspx

rsaphier

ASKER

Rockiroads,

I tried your suggestion but it produces the same results ... nothing happens. The batch file is created properly and can be executed outside of MSAccess without any problems. Any other suggestions?? Bumping the points up on this!!

rockiroads

can you run the dos command yourself? perhaps there is an issue with the script.
Also did u check for a HRGroupMembers.txt file? The directory it would be would be the running dir as I see no chdir.
If you want it at a certain place put the full path in.

eg
sCommand = "showmbrs " & strGroup & " > c:\temp\HRGroupMembers.txt"

rsaphier

ASKER

Ok, I learned that the bat file was generating the text file after all and I have sinced edited the code to include a full path as suggested.

HOWEVER .... and this is so frustrating .... I am still having a problem with the Shell command in MSAccess. When I run the bat from dos or by double clicking on the bat file, it runs perfectly and produces the expected text file containing the contents of security permissions for the referenced group. When I execute the bat file using MSAccess (trying all suggestions submitted) the text file is generated with no contents whatsoever.

What in the world am I missing?

'Bat File
showmbrs BHI-MASTER\BHBSS_SC_Groups_Admin >"C:\Security\HRGrpMembers.txt"

'Generated text when executed from dos or double click
Members of global group [BHI-MASTER\BHBSS_SC_Groups_Admin]:
   mcclwilh
   roberapd
   comewesj
   hillalam

Open in new window

rsaphier

ASKER

For whatever reason, and this makes no sense to me but changing to this .... and now everything works. Bat file produces text file and text file now contains content.

lngHWnd = Shell("c:\security\HRGrpMembers.bat")

rsaphier

ASKER

Thanks for everyone's help with this. I'm splitting the points because you both steered me into a direction that resolved the issue ... but still the issue is a mystery.

rockiroads

did u try cmd /c if its just the one line of code

Jeffrey Coachman

rockiroads,

My code is what I use to run a batch file form VBA.

I did not test the code posted, because my presumption was that the code created the batchfile, and that the issue was that the batch file would not run when called via Shell.

Can you get a standard batch file to run with shell?

Or are you saying that the batch file itself may be malformed?

Can either of you post the actual contents of this batch file, so I can give it a whirl?

;-)

Jeff

rockiroads

I gave it a test myself with my own batch file, not sure why it wouldnt run on my winxp. the batch file from op has already been posted here