Running .vbs file from Java

Question

I wrote a Java program that creates an Excel Workbook with at least 4 sheets & loaded into it some vb modules (by loading a template.xlsm with vb modules) Now I want to transform this Excel workbook into a PDF so I have this code in Excel macro VBA

Dim saveLocation As String
saveLocation = Application.ThisWorkbook.path
Dim ws As Worksheet
    For Each ws In Application.ThisWorkbook.Worksheets
        With ws.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
            .Orientation = xlLandscape
            .CenterVertically = True
            .CenterHorizontally = True
            .PaperSize = xlPaperTabloid
        End With
    Next
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

End Sub

I executed it from Excel VBA and its working fine However, what I want is to run it from Java directly after it's been created So what I did after writing the Excel Workbook (in Java) is the following

I wrote from Java into a text file having a VBS extension (module.vbs) the following code

Sub ExcelMacro()
 Dim objExcel 
 Dim objWorkbook 
 Set objExcel = CreateObject("Excel.Application") 
 Set objWorkbook = objExcel.Workbooks.Open("PATH\Cost Sheet_233.xlsm")
 objExcel.Run "'Cost Sheet_233.xlsm'!Module3.SaveActiveSheetsAsPDF" 
 objWorkbook.Save 
 objWorkbook.Close 
 objExcel.Quit 
End Sub

then I executed the following command

try {
 Runtime.getRuntime().exec( new String[] { "C:\\Windows\\System32\\wscript.exe", filePath + "\\module.vbs"} );
 }
catch (Exception ex) 
{
 ex.printStackTrace();
 }

The output is/should be a PDF file having all Excel sheets Again, when I run the code from Excel VBA, it works fine. Both the one in the module.vbs and the macro work fine and generate the PDF File, but the Java exec statement isn't Of course, the paths are right too 100% (wscript.exe exists) I also tried cscript.exe but didn't work

What could the problem be or did I miss something?

Apache poi cannot help here as it not interacts with Excel or the Windows operating system in any way. — Axel Richter
Try removing the Sub and EndSub lines from module.vbs or add a line Call ExcelMacro() — CDP1802

Lou Lou · Accepted Answer · 2021-08-28T07:18:29

I figured it out yesterday after asking the question. I tried other ways in writing the .vbs file and the following seemed to work when I call it from Java

Set objShell = CreateObject("WScript.Shell")
Dim cur
cur="Path"
WScript.Echo cur

ExcelMacroExample
Sub ExcelMacroExample()

    Dim xlApp
    Dim xlBook
    Dim xlsFile
    xlsFile=cur & "\Cost Sheet_274.xlsm"
    Set xlApp=CreateObject("Excel.Application")
    Set xlBook =xlApp.Workbooks.Open(xlsFile)
    xlApp.Run "SaveActiveSheetsAsPDF"
    xlBook.Save
    xlApp.Quit

End Sub

and this the Java code that runs the .vbs file

try {
  Runtime.getRuntime().exec(new String[] { "C:\\Windows\\System32\\wscript.exe", filePath + "\\module.vbs" });

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

Running .vbs file from Java

1 Answers