run Shell command/ batch file with multiple lines from VBA

Question

I have 2 lines which i have to run in cmd everyday multiple times. The lines resemble those below:

set_someclasspaths.bat
java -Xmx1024m foo.bar.stuff.Dashboard -var varone -from 20160701 -to 20160731 -outputdir c:\stuff -ir @ -dashboard

I have thought of two ways to automate this. The first is a batch file and the second is send keys. I have made a file labeled it something.bat and pasted in the above two lines. This quickly opens a window in cmd and closes again (even if i add pause at the end), and doesnt do what i'd expect- infact as far as i can tell it runs the first command then nothing.

The send keys method only seems to run the first line, I have tried:

Sub testcreate()
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1

wsh.Run "cmd.exe /K C:\a\b\set_someclasspaths.bat", windowStyle, waitOnReturn
wsh.sendkeys "java -Xmx1024m foo.bar.stuff.Dashboard -var varone -from 20160701 -to 20160731 -outputdir c:\stuff -ir @ -dashboard"
End Sub

My batch file and java ability are dubious at best, it would seem like the neater way to solve this is the batch file, so i would prefer to do that.

I guess i am passing parameters to a java script in the second line, im not sure if there is syntax for this, and without learning some java and batch file stuff I've ground to a halt.

To clarify, my question is two fold: first is the batch file the better way to do this? Second, why is this not working?

Help greatly appreciated.

Anon Coward · Accepted Answer · 2016-07-26 16:00:29Z

3

When you call one batch file from another, control is passed to the second and it doesn't return.

If you'd like it to return, you need to pass in call, so your batch file will look like this:

call set_someclasspaths.bat
java -Xmx1024m foo.bar.stuff.Dashboard -var varone -from 20160701 -to 20160731 -outputdir c:\stuff -ir @ -dashboard

answered Jul 26, 2016 at 16:00

Anon Coward

10.9k3 gold badges31 silver badges42 bronze badges

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

Comments

SierraOscar · Accepted Answer · 2016-07-26 15:59:46Z

1

What about:

Sub CreateAndRun()

Dim batchContents As String
Dim batchFile     As String
Dim FF            As Byte

batchFile = Environ$("USERPROFILE") & "\temp.bat"

batchContents = "CALL set_someclasspaths.bat" & vbCrLf & _
                "java -Xmx1024m foo.bar.stuff.Dashboard -var varone -from 20160701 -to 20160731 -outputdir c:\stuff -ir @ -dashboard"

FF = FreeFile

Open batchFile For Output As #FF
    Print #FF, batchContents
Close #FF

CreateObject("WScript.Shell").Run batchFile, 1, True

DoEvents

Kill batchFile

End Sub

answered Jul 26, 2016 at 15:59

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Comments

daZza · Accepted Answer · 2016-07-26 16:02:04Z

1

Create a wrapper batch file like wrapper.bat and paste your two commands in there:

call set_someclasspaths.bat
java -Xmx1024m foo.bar.stuff.Dashboard -var varone -from 20160701 -to 20160731 -outputdir c:\stuff -ir @ -dashboard
pause

This should execute both the batch file and your java statement and then keep the window open.

Instead of having to create a batch file via VBA all the time, you can just simply execute it when you click on a button or something.

answered Jul 26, 2016 at 16:02

daZza

1,6872 gold badges30 silver badges52 bronze badges

Collectives™ on Stack Overflow

run Shell command/ batch file with multiple lines from VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related