none
Table's worksheet/id doesn't load on certain Excel Desktop builds RRS feed

  • Question

  • We have built an Excel Task pane add in that primarily works with tables. In few versions of Excel we get an error when we try to load tables from workbook.

    Sample code to load tables from workbook:

    $scope.findTables = function () {

        let tables;
        Excel.run(function (ctx) {
            let xlTables = ctx.workbook.tables;
            xlTables.load(["count", "items"]);
            return ctx.sync(xlTables).then(function (xlTables) {
                tables = new Array(xlTables.count);

                let loader = OfficeExtension.Promise.resolve({});
                xlTables.items.forEach(function (xlTable, i) {

                    loader = loader.then(function () {
                        xlTable.load(["id", "name", "worksheet/id"]);
                        let tblRange = xlTable.getRange();
                        prepare(tblRange);
                        return ctx.sync(tblRange);

                    }).then(function (tblRange) {

                        let keyCellRow = tblRange.rowIndex + rowOffset,
                            keyCellColumn = tblRange.columnIndex + colOffset;

                        let keyRange = ctx.workbook.worksheets.getItem(xlTable.worksheet.id).getCell(keyCellRow, keyCellColumn); //error thrown at this line
                        keyRange.load(["formulas", "values"]);

                        return ctx.sync().then(function () {
                            return keyRange.formulas[0][0] ? keyRange.formulas[0][0] : keyRange.values[0][0];
                        });

                    }).then(function (keyValue) {

                        if (!keyValue) return;

                        let tableRef = {
                            id: xlTable.id,
                            sheet: xlTable.worksheet.id,
                            name: xlTable.name,
                            key: keyValue
                        };
                        tables[i] = tableRef;
                    });
                });
                return loader;
            });
        }).then(function () {
            console.log("No of Tables", tables.length);
            app.showNotification("No of Tables detected: " + tables.length);
        }).catch(function (error) {
            app.showNotification("Error in detecting tables");
            console.log("Error: " + error);
            logError(JSON.stringify(error));
            if (error instanceof OfficeExtension.Error) {
                console.log("Debug info: " + JSON.stringify(error.debugInfo));
            }
        });
    }

    The error we get is property id of worksheet not loaded. Note that here in sample codexlTable.load(["id", "name", "worksheet/id"]) and context.sync both are called only after that worksheet.id is accessed. But it still throws the error.

    Here is the detailed error:

    {
        "name": "OfficeExtension.Error",
        "code": "PropertyNotLoaded",
        "message": "The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.",
        "traceMessages": [],
        "innerError": null,
        "debugInfo": {
            "code": "PropertyNotLoaded",
            "message": "The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.",
            "errorLocation": "Worksheet.id"
        },
        "stack": "PropertyNotLoaded: The property 'id' is not available. Before reading the property's value, call the load method on the containing object and call \"context.sync()\" on the associated request context.\n   at t.throwIfNotLoaded (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:261181)\n   at id.get (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:286763)\n   at Anonymous function (https://smart-client.ngrok.io/app/Home.js:44:29)\n   at yi (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246431)\n   at st (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246518)\n   at d (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:246338)\n   at c (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:21:244924)"
    }

    Note that this happens only on specific build versions of Excel. We have observed this issue on 16.0.4498.1000 and 16.0.4549.1000 build versions.

    Can you help us as to why this is happening and possible way to fix this?

    Tuesday, September 26, 2017 11:26 AM