Solved

VBA Batch File Not Executing On Shell

Posted on 2010-09-08
14
706 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Backup Your Microsoft Windows Server®

Backup 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.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now