workbook.calculateFormula() doesn't force Excel to refresh macro

Aspose.Cells Product Family
1

Hi, i was using v8.6.1 of Java Cells and i want to migrate to 20.6 but there is an issue.

My code changes some cells values and these values are used as argments for some VB macro.

In v8.6.1 it’ok, on openning the file with Excel, Excel executes macro well. But with 20.6, Excel displays #NAME instead of execute macro.

04.zip (22.6 KB)

Here is the code :

try {
        Workbook wb = new Workbook("Classeur_Base.xlsm");

        wb.getWorksheets().get(0).getCells().get(1, 3).setValue("Hey");
        wb.calculateFormula();
        wb.save("Classeur_Copié.xlsm");

    } catch (Exception ex) {
        Logger.getLogger(TestMacro.class.getName()).log(Level.SEVERE, null, ex);
    }

The base workbook looks like :
image.png (744 Bytes)

And the result workbook looks like :
image.png (762 Bytes)

The macro just add “__” and time to its arg.
Here is the code :

Public Function AfficherPhrase(Prm As String) As String
AfficherPhrase = “__” & Prm & " " & Minute(Now()) & “:” & Second(Now())
End Function

Is in v20.6 an option to keep the same “Excel re-execute” macro on openning effect ?

Thanks for help

2

@uetac,

I have tried the scenario with both ver 8.6.1 and 20.6 but could not observe any difference as both the output files show #NAME when it is opened and then I need to enable the macros. Could you please share which version of MS Excel are you using and other environment details? We will try to reproduce the issue here and provide assistance accordingly.

3

thanks a lot for your first response.

here are the used Excel files :
04.zip (22.6 KB)

  • machine : Windows 10, Netbeans 12, JDK 1.8 (Oracle)
λ java -version
java version "1.8.0_251"
Java(TM) SE Runtime Environment (build 1.8.0_251-b08)
Java HotSpot(TM) 64-Bit Server VM (build 25.251-b08, mixed mode)
  • The maven project source & target are for 1.8 .
  • Excel 2010

These are my steps that i follow to reproduce the issue :

  1. Excel : clean all trusted documents
  2. run the app and generate the “Classeur_Copié.xlsm” file
  3. Open the generated file, Excel asks me to click to validate “macros”
  4. I click to “validate”
  5. the macro runs fine
  6. I quit Excel without saving

With the v20.6 :
7. i open the generated file again : Excel doesnt ask me to validate macro; and #NAME appears in the cell

With the v8.6.1:
7. i open the generated file again : Excel doesnt ask me to validate macro; and the cell is correctly displayed.

Between the two tests, I build the project after changing Cells version.

Theses steps are more simpler if i speficy to Excel that the destination directory is trusted. But the result is the same between the two versions of Cells.

thx for help.

4

@uetac,
We have observed this issue and logged it in our database for further investigation and fix. You will be notified here once any update is ready to share.

This issue is logged as
CELLSJAVA-43236 - Workbook calculate formula does not show value after enabling macro while opening output file next time

5

ok thank you !

6

@uetac,
You may please modify the code as follows and test the scenario again. It will show values fine when second time output file is opened in MS Excel.

	try {
        Workbook wb = new Workbook("Classeur_Base.xlsm");

        wb.getWorksheets().get(0).getCells().get(1, 3).setValue("Hey");
        wb.calculateFormula();
        wb.getSettings().setReCalculateOnOpen(true);//<=======NOTE THIS LINE
        wb.save("Classeur_Copié_20.6.3.xlsm");

    } catch (Exception ex) {
    	System.out.println(ex.getMessage());
    }
7

i’ve tried this “hack” and it works fine for me.
Thank you very much !

8

@uetac,

Good to know that your issue is sorted out by the suggested line of code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.