Attempt to run .bat script from VBA fails without error code

Question

SUMMARY: I am trying to run a .bat script on a Windows machine using Excel VBA. I have found references online describing how to do this, and I have followed the instructions contained therein. However, my .bat file is not running.

DETAILS: My .bat file ("test.bat") contains the following code:

echo off
echo This is a test...
pause
exit

It is located on my desktop, with the following path: "C:\Users\User1\Desktop". When I double-click this .bat file or call it from the command line, it exhibits the desired behavior. Namely, it pauses and waits for me to press any key.

I am trying to run this same .bat file from Excel VBA using the following code:

Sub testBatchScript()

    'Print a message to the immediate window to confirm subroutine execution
    Debug.Print "The subroutine is running."

    'Initialize a windows shell object
    Dim wsh As WshShell
    Set wsh = New WshShell

    'Construct the full path to the .bat file
    Dim fullPath As String
    fullPath = Chr(34) + "C:\Users\User1\Desktop\test.bat" + Chr(34)

    'Run the .bat file
    Dim eCode As Long
    eCode = wsh.Run(fullPath, waitonreturn:=True, windowstyle:=1)

    If eCode <> 0 Then
        MsgBox ("An error occurred.")
    End If

End Sub

When I run this subroutine, nothing happens. No CMD window pops up, and no message box indicating an error pops up. However, I do know that the subroutine is running, due to the message that appears in the immediate window.

I'm stumped as to why this isn't working. What's even more perplexing is that I was able to successfully call .bat scripts from VBA as recently as a few weeks ago..

Anyone have any explanation or suggestions?

UPDATE1: Following @Dhamo's suggestion in the comments below, I have updated the VBA code with error handling:

Option Explicit

Sub testBatchScript()

    On Error GoTo EH:

    'Print a message to the immediate window to confirm subroutine execution
    Debug.Print "The subroutine is running."

    'Initialize a windows shell object
    Dim wsh As WshShell
    Set wsh = New WshShell

    'Construct the full path to the .bat file
    Dim fullPath As String
    fullPath = Chr(34) + "C:\Users\User1\Desktop\test.bat" + Chr(34)

    'Run the .bat file
    Dim eCode As Long
    eCode = wsh.Run(fullPath, waitonreturn:=True, windowstyle:=1)

    If eCode <> 0 Then
        MsgBox ("An error occurred.")
    End If

    Exit Sub

EH:
    MsgBox ("The error description is: " & Err.Description)

End Sub

When I run this code, still nothing happens.

UPDATE2: I ran the test code again this morning, after restarting my computer, and this time I got a pop-up message saying: "The error description is: Permission denied". I then ran the code again to see if I could replicate this error message, and there was no pop-up! Furthermore, I can't get the error message to reappear no matter how many times I run the code. So restarting seems to have done something...

The "Permission denied" message suggests that it's a security issue. Anyone have any ideas for solving it?

Have you added 'Windows Script Host Object Model' reference ? if not already please add it and try [? == VBA window, click Tools > References] — Dhamo
– Dhamo, Commented Apr 2, 2019 at 23:51
@Dhamo: I'm sorry, I don't understand what you mean by "try [? == VBA window...". — Garrett
– Garrett, Commented Apr 2, 2019 at 23:55
Add option explicit in the first line and also enable error handler [on error goto EH:] and also define the EH [EH: msgbox err.description]. At least you will get to know the actual issue to some extent. The above code is working fine for me [I use v2016] — Dhamo
– Dhamo, Commented Apr 2, 2019 at 23:56
If you've already added the reference, you can ignore further comment — Dhamo
– Dhamo, Commented Apr 2, 2019 at 23:57

John Coleman · Accepted Answer · 2019-04-03 00:34:11Z

1

Not an answer, but too long for a comment:

If you put the following into a file, save it as test.vbs (not as a text document: notepad will create test.vbs.txt if you are not careful):

Dim wsh, fullPath, ecode
Set wsh = WScript.CreateObject("WScript.Shell")
fullPath = "C:\programs\test.bat"
ecode = wsh.Run(fullPath, 1, True)
msgbox(ecode)

(with the full path adjusted to your situation). Save it to e.g. the desktop and click the icon. What happens?

answered Apr 3, 2019 at 0:34

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Garrett Over a year ago

So I suppose that means that Excel is the problem? Any thoughts on how to fix it?

John Coleman Over a year ago

Your guess is as good as mine. I've never encountered something like that. It is almost like Excel is sending a keystroke to the command window before you can see it.

Garrett Over a year ago

I've changed the .bat file so that it instead of waiting for a keystroke, it creates a separate .txt file. This .txt file is not created when I try to call the .bat script from VBA. So it's not that Excel is sending a keystroke before I can see the window.

Ricardo A Over a year ago

It could be a windows security "feature". It may not like doing Excel -> VBA -> Bat file, but it likes just doing VBS -> Bat file. Also did you try removing these: , waitonreturn:=True, windowstyle:=1 and just doing 1, True in Excel like the VBS?

Garrett Over a year ago

Yes, I'm afraid it might be a security thing... But, if so, why does it seem to only affect me?

|

Collectives™ on Stack Overflow

Attempt to run .bat script from VBA fails without error code

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related