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

rsaphierAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
I guess bat is not a recognised executable name so perhaps try using the api way of ShellExecute
eg

add this in a module

Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
  (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long
   


then call like this


ShellExecute Application.hWndAccessApp, "Open", strFile, "", vbNullString, 0


I am assuming this is in office vba? like Access? if not access then first argument needs to change
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

<nothing happens!  >
Nothing happens? or are you saying that the DOS window open, runs the command(s), and closes too fast for you to notice anything happening?
;-)

Since you did not post the actual Shell code you used to execute the batch file, or posted the contents of the actual batch file, it is hard to say what may or may not have happened.

FWIW, this has worked fine for me for years:
Dim varApp As Variant
varApp = Shell("C:\Yourfolder\YourBatchFile.bat", vbNormalFocus)


;-)

JeffCoachman
0
 
rsaphierAuthor Commented:
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

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
rsaphierAuthor Commented:
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.
0
 
rsaphierAuthor Commented:
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


0
 
rockiroadsCommented:
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
0
 
rsaphierAuthor Commented:
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!!
0
 
rockiroadsCommented:
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"
0
 
rsaphierAuthor Commented:
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

0
 
rsaphierAuthor Commented:
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")

0
 
rsaphierAuthor Commented:
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.
0
 
rockiroadsCommented:
did u try cmd /c if its just the one line of code
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
rockiroadsCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.