[SOLVED] How to run a .bat file from excel vba

How to run a .bat file from excel vba

I have a .bat file that runs if I double click on it. But it doesn't run when I try to run it from Excel vba like this:

Const BatchfileName = "C:\Users\TB\Desktop\UpdateReports_NEWDB\myBatch.bat"
Dim wsh2 As Object
Set wsh2 = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn2 As Boolean: waitOnReturn2 = True
Dim windowStyle2 As Integer: windowStyle2 = 1 'To minimize the Command prompt window use 2, to hide it use 0 (zero),to show it type 1(one)
wsh2.Run BatchfileName, windowStyle2, waitOnReturn2

I am trying to run it on windows 10 32bit. I am mentioning this because I have another laptop with windows 10 64bit and it runs from excel vba there. Do I need to have R on my path in order to run this shell?

BatchfileName contains: cd %1 R CMD BATCH current_model.r

Solution

I suppose "myBatch.bat" is

cd %1
R CMD BATCH current_model.r

Thus, you need to provide a path as the single parameter for this file.

Const BatchfileName = "C:\Users\TB\Desktop\UpdateReports_NEWDB\myBatch.bat"
Const path = "C:\Users\TB\Desktop\UpdateReports_NEWDB" ' replace it with the actual path you need

Shell "cmd /c " & BatchfileName & " " & path, vbNormalFocus

Consider what cd doesn't change the current drive. If the path refers to another drive, you need to change the current drive first.

If the (current) folder content isn't required for R, you can run

Shell "R CMD BATCH path\current_model.r", vbNormalFocus

replacing 'path' by the actual path to 'current_model.r'.