?
Solved

VBA Batch File Not Executing On Shell

Posted on 2010-09-08
14
Medium Priority
?
754 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 1400 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 600 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

862 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