How to run VBS function with parameters from Java and assign result to variable

Question

I have this excel macro:

Function Calculate_Something(StartDate As Date, EndDate As Date) As Double

//some math is here, not important

Calculate_Something = Result
End Function

And I want to pass my dates to this macro, execute it in my Java program and finally get returned value and assign it to my value in Java.

I have created VBS script with this function and try to execute it like this in Java:

 String[] parms = {"wscript", "calc.vbs", "2017-02-06 09:38:36", "2017-02-06 12:47:41"};
 Runtime.getRuntime().exec(parms);

But it didn't work. Do you know how could I do that?

Answer 1

You will want to use cscript.exe instead of wscript.exe, they are both the same host but one is designed for GUI while the other is designed for the command line.

Modify the VBScript function to output the Result to screen (the executed command output stream) then retrieve it using the Process object derived from calling Runtime.getRuntime().exec(parms);.

There is a method of the Process object called getInputStream() which should allow you to access and read the value you returned by the script output.

try {
    String[] parms = {"cscript", "calc.vbs", "2017-02-06 09:38:36", "2017-02-06 12:47:41"};
    Process p = Runtime.getRuntime().exec(parms);

    // Get Input Stream from the Process
    BufferedReader is = new BufferedReader(new InputStreamReader(p.getInputStream()));

    // Do something with stream, read etc.
    String line;
    while ((line = is.readLine()) != null)
        System.out.println(line);

} catch (Exception ex) {
    ex.printStackTrace();
}

---

Useful Links

• vbscript output to console
• What is InputStream & Output Stream? Why do we use them and when do we use each of them?
• Understanding getInputStream and getOutputStream