VBA - Execute or Run .bat File Inside Macro

Question

I have been attempting to automate a series of administrative events for some of the users where I work by creating scripts and macro's and so on..

These scripts and macros work great, however, I would like to make a the process even easier for the users by running a single batch file that will systematically execute the scripts and macros.

The batch file I currently have, calls all the scripts one by one, and the very last script opens one of the xlsm workbooks which contains a few macro's in it - and here is where the issue is - There are still scripts to be executed but they can only be executed once this workbook has executed all its macros.

So my initial thought was to test if the workbook is open, if it is, delay the execution of the next script by a minute or so, then test again and again... until it is closed.. Then I thought perhaps it would be easier to execute the next set of scripts (also in a batch file) from within a macro.

So, I have this code:

Sub Run_BAT()

    Set obj = CreateObject("Wscript.Shell")

    obj.Run Chr(34) & "X:\Test\" & "Termination Reports Scripts\" & "Execute_Terminations.bat" & Chr(34), 0, True

    Set obj = Nothing

End Sub

Which gives me an error:

Permission Denied

Then there's this code:

Sub WriteAndRunBatFile()

Call Shell("X:\Test\Termination Reports Scripts\Execute_Terminations.bat")

End Sub

Which gives me the error:

Invalid procedure call

Any and every single code sample that contains the "Shell" command gives this error.

Harun24HR Harun24HR · Accepted Answer · 2018-04-19T12:01:01

(1) Check permission of user of that X directory.
(2) Test the code by removing spaces from directory name.

Also try the following code (Please try it by removing spaces from directory name).

Sub Button1_Click()
    Call Shell("CMD.EXE /C " & "X:\Test\Termination_Reports_Scripts\Execute_Terminations.bat")
End Sub

VBA - Execute or Run .bat File Inside Macro

4 Answers