Running Personal VBA code with Jacob in Java

Question

Before asking my question I searched a lot but I can't find an answer. In fact, I managed to run a custom macro from Java but before finishing it shows me an error message saying type mismatch on a line of code in VBA beside when I run it manually it works perfectly.

Could anyone help me?

I put here the code line which causes the error:

Det_Lundi = Evaluate("TEXT(DATE(" & Annee & ",1,3)-WEEKDAY(DATE(" & Annee & ",1,3))-5+(7*" & semaine & ")+" & NumJour & ",""dd/mm/yyyy"")")

As I said if I run it manually it works perfectly also I test the method which I wrote in java which runs macros and it worked with other macros the only problem is with this line.

Thanks for your answer. I really don't agree with cause it worked when I run it manually here I post my VBA code:

Function Det_Lundi() As Date Dim ws_c As Worksheet Dim Annee As Integer, semaine As Integer, NumJour As Integer

semaine = Format(Worksheets("Abs").Range("a1").Value, "ww", vbMonday, vbFirstFourDays)

Annee = Year(Worksheets("Abs").Range("a1").Value) 'semaine = 16

NumJour = 0 ' 0=Lundi, 1=Mardi ...

Det_Lundi = Evaluate("TEXT(DATE(" & Annee & ",1,3)-WEEKDAY(DATE(" & Annee & ",1,3))-5+(7*" & semaine & ")+" & NumJour & ",""dd/mm/yyyy"")")

End Function

also i put my method in java:

public static void UpdateFile(final File file, final String macroName) {
    ComThread.InitSTA();
    final ActiveXComponent excel = new ActiveXComponent("Excel.Application");

try {
    // This will open the excel if the property is set to true
    // excel.setProperty("Visible", new Variant(true));

    final Dispatch workbooks = excel.getProperty("Workbooks")
            .toDispatch();
    final Dispatch workBook = Dispatch.call(workbooks, "Open",
            file.getAbsolutePath()).toDispatch();

    // Calls the macro
    final com.jacob.com.Variant result = Dispatch.call(excel, "Run", macroName);

    // 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 e) {
    e.printStackTrace();
} finally {

    excel.invoke("Quit");
    ComThread.Release();
}
}

Answer 1

Your issue might be with the date as internally, Excel understands only English so dd/mm/yyyy might actually be mm/dd/yyyy. Though this is just a guess.

If that doesn't help, see if this can help.