Java and VBScript to populate and run macro throwing error

Question

I have an Excel file which has a macro and I would like to automate the process. I have Java code which fills the Excel columns and I have written the VBScript to run the macro in the Excel.

My Java code is (I pass the Excel fileName which has the macro)

public void excelupdate(String fileName) {
    FileInputStream file = null;
    FileOutputStream out = null;
    try {
        file = new FileInputStream(new File(fileName));
        HSSFWorkbook yourworkbook = new HSSFWorkbook(file);
        HSSFSheet sheet1 = null;
        for (int i = 0; i < yourworkbook.getNumberOfSheets(); i++) {
            if (yourworkbook.getSheetName(i).contains("Sheet-Macro")) {
                sheet1 = yourworkbook.getSheetAt(i);
            }
        }

        Cell cell = null;
        int rowValue = 10;
        for (int i = 0; i < list.size() - 1; i++) {
            cell = sheet1.getRow(rowValue).getCell(2);
            cell.setCellValue(list.get(i));
            rowValue++;
        }

        Cell cell1 = null;
        int rowValue1 = 10;
        for (int j = 0; j < Input1list.size() - 1; j++) {
            cell1 = sheet1.getRow(rowValue1).getCell(3);
            cell1.setCellValue(Input1list.get(j));
            rowValue1++;
        }

        Cell cell2 = null;
        int rowValue2 = 22;
        for (int k = 0; k < Input2list.size() - 1; k++) {
            cell2 = sheet1.getRow(rowValue2).getCell(4);
            cell2.setCellValue(Input2list.get(k));
            rowValue2++;
        }

        out = new FileOutputStream(("C:\\Users\\Desktop\\EXCEL.xls"));
        yourworkbook.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (file != null) {
            try {
                file.close();
            } catch (Exception e) {
            }
        }
        if (out != null) {
            try {
                out.close();
            } catch (Exception e) {
            }
        }

The Java code runs on Apache Poi to fill in the columns and moves the Excel file to a particular directory and then I have the below VBScript to run the macro:

Dim objXL
Set objXL = CreateObject("Excel.Application")
Set objWorkbook = objXL.Workbooks.Open("F:\testmacro\testmacro\EXCEL.xls")
objWorkbook.Sheets("AD stages").Cells(6, 4) = "F:\set1\set.txt"
objXL.Application.DisplayAlerts = False
objXL.ActiveWorkbook.Save
objXL.Application.Run "macro_cal"
objXL.ActiveWorkbook.Save
objXL.ActiveWorkbook.Close
objXL.Application.DisplayAlerts = True
objXL.Application.Quit
WScript.Echo "ExCEL file updated successfully"
WScript.Quit 
Set objXL = Nothing

I call the above VBscript from the java as below,

File file = new File(excelFilename);
            file.setExecutable(true);
            file.setReadable(true);
            file.setWritable(true);

        Runtime runtime = Runtime.getRuntime();
        try {
            String sample="cmd /c start "+vbScript+" "+"\"" +excelFilename + "\"" + " "+"\"" +outFile + "\"";
            System.out.println(sample);
            Process process1 = runtime.exec(sample);


        } catch (IOException e) {
            logger.error(e);
        }

But the problem is, once the Java populates the Excel columns and save the file, the file becomes protected and hence the VBScript is throwing an error stating it can't open/run the macro in protected Excel.

Any advice?

Answer 1

Thanks to @AxelRitcher, the solution is "Please read about Error message in Microsoft Office: "Office has detected a problem with this file". So seems as if the location F:\testmacro\testmacro is not a trusted location for Excel files having macros in it."