0

In MS excel file user has set that formulas are manually recalculated. I would like to force auto recalculate with LibXL, after I write data to the file. Is this possible somehow ?

I have searched documentation, and I couldn't find anything related. LibreOffice is recalculating formulas automatically, after I open the file created with LibXL, but Microsoft Office does not.

There is no point in showing my code. Either LibXL has flag / a way to do this or not.

After I am done with creating file with LibXL, and I open the file in MS Office, I would expect that formulas are automatically executed.

Note: I am using PHP

user11951115
  • 1
  • 1
  • If the user has forced manual calculation, don't you think it's their problem if the values are not correct? – Andreas Oct 29 '19 at 17:27
  • @Andreas - users have no clue what they are doing, it is company requirement that we trigger the formulas automatically in every case. – user11951115 Oct 30 '19 at 09:28

1 Answers1

0

If the user opens the file for the first time and no windows open on user's computer, calculation mode is set to "Auto" by defualt on MS excel. But prior to opening your workbook if somehow user activated manual calculation mode on his other workbooks, your file will be opened in manual mode as well.

I am not sure how PHP involved in this. But to force auto calculation you can add a simple open event macro to your file. 

Sub Workbook_Open()
 Calculate
End Sub

Copy this into workbook object in visual basic window. This macro will be executed every time the file is opened.

Hope this helps

onur rr
  • 57
  • 1
  • 9
  • Thanks for replay. LibXL can not add macros to excel files. So question is: how to programmatically manipulate excel files before user can open them and do anything (with libxl or php). – user11951115 Nov 07 '19 at 09:42
  • As far as I understand, we are not talking about manipulating excel files here, we want to manipulate Excel's behavior as a program. So this may be solved with 2 approaches. There is a way in Excel options > Formulas tab that sets the calculation method which you can modify with Windows Group Policy. You can force users computers to set this option to auto. The second way might be creating a 2nd file with a macro that recalculate your formulas and then outputting your final excel file. Other than these, A quick google search listed some ways to include a macro with the excel file in PHP. – onur rr Nov 10 '19 at 20:13