0

I created a Java app that does a few things:

1.based on some input it generates some text - works on other computers

2.based on an input it modifies a excel file - work on other computers

3.it runs a macro from the excel file that it modified earlier - works only on my computer

This is the part of the code that edis the excel file and runs the macro:

try {

    File myFile = new File(atmScriptGeneratorPathTxt.getText() + "\\ATM Script Generator.xlsm");
    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Cell myCell = null;
    myCell= mySheet.getRow(2).getCell(2);
    myCell.setCellValue(newRNCCDRFolderTxt.getText());

    fis.close();
    FileOutputStream output_file =new FileOutputStream(new File(atmScriptGeneratorPathTxt.getText() + "\\ATM Script Generator.xlsm"));
    myWorkBook.write(output_file);
    output_file.close();
    myWorkBook.close();

    String macroName = "'ATM Script Generator.xlsm'!ATM_Script_Generator.ATM_Script_Generator";             
    ComThread.InitSTA();
    ActiveXComponent excel = new ActiveXComponent("Excel.Application");

    try {
        //This will open the excel if the property is set to true    

        Dispatch workbooks = excel.getProperty("Workbooks") .toDispatch();
        Dispatch workBook = Dispatch.call(workbooks, "Open", myFile.getAbsolutePath()).toDispatch();                                                                           
        // Calls the macro
        final Variant result = Dispatch.call(excel, "Run", new Variant(macroName));
        System.out.println(result);
        // Saves and closes
        Dispatch.call(workBook, "Save");                                                    
        com.jacob.com.Variant f = new com.jacob.com.Variant(true);
        Dispatch.call(workBook, "Close", f);

        } catch (Exception error) {
            error.printStackTrace();
        } finally {

            excel.invoke("Quit", new Variant[0]);
            ComThread.Release();
        }
    }
    catch(Exception err) {
        System.out.println(err);
    }
}

These are the libraries that I am using for this app:

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jacob.activeX.ActiveXComponent;

import com.jacob.com.ComThread;

import com.jacob.com.Dispatch;

import com.jacob.com.Variant;

Does anyone knows why this app works only on my computer? If there is any more info that someone needs in order to figure out what is the problem, let me know.

P.S.
  • 15,970
  • 14
  • 62
  • 86
Mihai Lucian
  • 1
  • 2
  • Does the macro work ok, and the path/name correct, would be the 1st step for me, then I'd try a simpler macro to see if that executes, in a new wb, perhaps just a hello message box. does application.run macroname work from another wb? – Nathan_Sav Dec 06 '16 at 10:19
  • What error on other computers? [JACOB](http://danadler.com/jacob/)->[FAQ](http://danadler.com/jacob/jacobfaq.html)->If I use Jacob in my application do I have to install jacob.dll on client side?... – Axel Richter Dec 06 '16 at 10:20
  • I don't receive any specific error. I mean I don't know how to catch it because on other computer I only have the exported app.jar. On other computers it only edits the excel but not runs the macro. – Mihai Lucian Dec 06 '16 at 11:21
  • I tried what you suggested. I created a simpler macro(just a msgBox) and the same thing happens. The macro starts on my computer but on other computer does not. If you have other suggestions I am willing to try. @Nathan_Sav – Mihai Lucian Dec 06 '16 at 14:22
  • Does it open the wb on the other users machine? I cant really assist you in debugging, break the java down, in the catch you have some code is this being executed, add message boxes in the java to track. – Nathan_Sav Dec 06 '16 at 14:33
  • have you tried, just running the macro on the other users machine and trying using application.run from another wb? – Nathan_Sav Dec 06 '16 at 14:53
  • I placed msgbox after every line and i found that on the other computer it gets stuck after this line "ComThread.InitSTA();" – Mihai Lucian Dec 06 '16 at 15:14
  • I tried the following: use ComThread.InitMTA() instead of ComThread.InitSTA() - not working; put in my main mth "System.setProperty("com.jacob.autogc", "true");" - not working. – Mihai Lucian Dec 06 '16 at 15:32
  • I commented the ComThread.InitSTA(); line and now it hangs at the next line: ActiveXComponent excel = new ActiveXComponent("Excel.Application"); . Again, on my computer the exported jar is working perfectly. It's very annoying :( @Nathan_Sav – Mihai Lucian Dec 06 '16 at 15:58
  • I foud what was the issue: jacob contains jacob-1.18-x86.dll(one for 64bit also) file and I had to add that files to C:\ProgFiles\Java\jre\bin . I copied .dll files there and now works. – Mihai Lucian Dec 06 '16 at 20:05
  • I am using eclipse and I copied jacob-1.18-x86.dll file to the resource folder of my project. Can someone help me copy this file automaticaly to the new computer to C:\ProgFiles\Java\jre\bin when I start the app? I want to include somehow this .dll file to my Java app because I want to give to users only the app and to work(without sending them .dll file and ask them to copy it to C:\ProgFiles\Java\jre\bin). @Nathan_Sav – Mihai Lucian Dec 06 '16 at 20:09
  • I found this post http://stackoverflow.com/questions/1611357/how-to-make-a-jar-file-that-includes-dll-files and a user call Bostone gived that solution but I don't understand exactly how to do it. I am new to java and also on stackoverflow and I don't know exactly how to contact him and my reputation is to low and I can't comment to the above post. – Mihai Lucian Dec 06 '16 at 20:11

1 Answers1

0

Jacob Libray contains two .dll files:

jacob-1.18-x64.dll

jacob-1.18-x86.dll

I copied this two .dll files to: C:\Program Files\Java\jre1.8.0_111\bin and it worked after.

Mihai Lucian
  • 1
  • 2