33

I'm dealing with a problem with libXL and Office365. I created an Excel file with Office 365: a simple formula which shows the content of a cell from another sheet. Then I proceeded to write something in that source cell through libXl. When I open the output file the formula is not calculated until I press CTRL+ALT+SHIFT+F9.

If I create the xlsx file from Office 2013 then the formula is correctly updated.

Couldn' find anything on their website whether O365 is supported or not.

Here's the code to reproduce the issue, (I can provide the two input xlsx files if needed):

#include "stdafx.h"
#include "libxl.h"

using namespace libxl;

int main()
{   
    Book* book = xlCreateXMLBook();

    // xlsx file created by Office 2013
    if (book->load(L"office2013.xlsx"))
    {
        Sheet* sheet = book->getSheet(0);
        if (sheet)
            sheet->writeNum(2, 2, 42);

        book->save(L"okay.xlsx"); // works correctly when opened
    }

    // xlsx file created by O365
    if (book->load(L"office365.xlsx"))
    {
        Sheet* sheet = book->getSheet(0);
        if (sheet)
            sheet->writeNum(2, 2, 42);

        book->save(L"bugged.xlsx"); // must press CTRL+ALT+SHIFT+F9 to see '42' in the second sheet
    }
    
    book->release();
    
    return 0;
}

This is the source sheet (number 42 written by the above code): source sheet

This is the not working formula (written in Excel): not working formula

Thank you

Burak
  • 2,251
  • 1
  • 16
  • 33
Davide
  • 439
  • 3
  • 8
  • It's not an Excel setting, but a bug in the libXL library related to the new version of Excel. I have the same issue, but I have not found any workround. Yet. I am staring a bounty.... – Redax Jun 13 '18 at 08:22
  • Do you have auto calculate on in the Office 365 Excel? Check this article: https://support.office.com/en-ie/article/change-formula-recalculation-iteration-or-precision-f38c7793-0367-41ce-b892-dfe54946bd76#__toc305944076 I have been fooled in some vbs-script I've created for Excel , with calculation off as default for some Office 365 installations. Check out formulars -> Calculation and check if everything is automatic on the workbook there. Edit: Please note that I've zero experience with LibXL, but plenty in vbs for Excel. I also program c++, but I find vbs as a quicker way to get in goal for – Large Jun 04 '18 at 10:35

0 Answers0