Solved

VBA Batch File Not Executing On Shell

Posted on 2010-09-08
14
710 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:rsaphier
  • 7
  • 5
  • 2
14 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 350 total points
ID: 33632697
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 150 total points
ID: 33634287

<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
 

Author Comment

by:rsaphier
ID: 33636642
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rsaphier
ID: 33636673
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
 

Author Comment

by:rsaphier
ID: 33636694
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33636933
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
 

Author Comment

by:rsaphier
ID: 33637143
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33637966
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
 

Author Comment

by:rsaphier
ID: 33639214
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
 

Author Comment

by:rsaphier
ID: 33639472
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
 

Author Closing Comment

by:rsaphier
ID: 33639507
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33639543
did u try cmd /c if its just the one line of code
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33639634
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33639649
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question