Running Java jar file from excel

[chetanojha]

Hi,

I have an excel column in which I have created java command (using formulas etc based on other columns).
There are more than one cells in the columns with respective Java command.
I want to run them in loop so that when Java command execute it will create some files for me.

I just want to find out how to run the command in loop based on the values in the cell. The cell in the excel look like below:

Name of the column is "Java Command"

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD]Java Command[/TD]
[/TR]
[TR]
[TD]java -jar ABC.jar -t oca -p -o "LONDON" -cpu 02 -u ROLLNUMBER1 -x -z[/TD]
[/TR]
[TR]
[TD]java -jar ABC.jar -t oca -p -o "AMSTERDAM" -cpu 02 -u ROLLNUMBER2 -x -z[/TD]
[/TR]
[TR]
[TD]java -jar ABC.jar -t oca -p -o "NEWYORK" -cpu 02 -u ROLLNUMBER3 -x -z[/TD]
[/TR]
[TR]
[TD]java -jar ABC.jar -t oca -p -o "EDINBURGH" -cpu 02 -u ROLLNUMBER4 -x -z[/TD]
[/TR]
</tbody>[/TABLE]

Now on this very forum I found a code to run the java program from the excel. But i am not not sure how to run it in the loop:

Private Sub CommandButton1_Click()
    ChDir ThisWorkbook.Path
    Shell "java -jar ABC.jar -t oca -p -o "LONDON" -cpu 02  -u ROLLNUMBER1 -x -z"
End Sub

File ABC.jar is present in the same directory as this excel sheet.


Can anybody advice how can i run all the above Java command in loop at the click of the button?

Thanks

 

[John_w]

Try this. The Shell command line might need to specify the full path to ABC.jar, so the first Replace does that. Similarly, you might need the second Replace to specify the full path to java(.exe).

Private Sub CommandButton1_Click()
    Dim cell As Range, command As String
    For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        command = Replace(cell.Value, "ABC.jar", ThisWorkbook.Path & "\ABC.jar")
        'command = Replace(command, "java", "C:\path\to\java")
        Shell command, vbNormalFocus
    Next
End Sub

Shell runs each command without waiting for it to finish and immediately runs the next command. If you want each command to run and wait until it finishes before running the next command then use the Wscript.Shell Run method with its WaitOnReturn argument True, instead of the Shell function.

 

[chetanojha]

Try this. The Shell command line might need to specify the full path to ABC.jar, so the first Replace does that. Similarly, you might need the second Replace to specify the full path to java(.exe).

Private Sub CommandButton1_Click()
    Dim cell As Range, command As String
    For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        command = Replace(cell.Value, "ABC.jar", ThisWorkbook.Path & "\ABC.jar")
        'command = Replace(command, "java", "C:\path\to\java")
        Shell command, vbNormalFocus
    Next
End Sub

Shell runs each command without waiting for it to finish and immediately runs the next command. If you want each command to run and wait until it finishes before running the next command then use the Wscript.Shell Run method with its WaitOnReturn argument True, instead of the Shell function.

Hi John... This has worked brilliantly.. Thanks a lot.

 

[chetanojha]

Try this. The Shell command line might need to specify the full path to ABC.jar, so the first Replace does that. Similarly, you might need the second Replace to specify the full path to java(.exe).

Private Sub CommandButton1_Click()
    Dim cell As Range, command As String
    For Each cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
        command = Replace(cell.Value, "ABC.jar", ThisWorkbook.Path & "\ABC.jar")
        'command = Replace(command, "java", "C:\path\to\java")
        Shell command, vbNormalFocus
    Next
End Sub

Shell runs each command without waiting for it to finish and immediately runs the next command. If you want each command to run and wait until it finishes before running the next command then use the Wscript.Shell Run method with its WaitOnReturn argument True, instead of the Shell function.

Can I add one more column to the table as a flag i.e. Y or N. And run the Java command only when the value of the cell is Y?


[TABLE="class: grid, width: 750, align: left"]
<tbody>[TR]
[TD]Run Command?
[/TD]
[TD]Java Command
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]java -jar ABC.jar -t oca -p -o "LONDON" -cpu 02 -u ROLLNUMBER1 -x -z
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]java -jar ABC.jar -t oca -p -o "AMSTERDAM" -cpu 02 -u ROLLNUMBER2 -x -z[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]java -jar ABC.jar -t oca -p -o "NEWYORK" -cpu 02 -u ROLLNUMBER3 -x -z[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]java -jar ABC.jar -t oca -p -o "EDINBURGH" -cpu 02 -u ROLLNUMBER4 -x -z[/TD]
[/TR]
</tbody>[/TABLE]