Office Excel JS - Change Range values of table object

Question

I am working on an Excel add-in using the Office JS API. I am experiencing an issue when updating the values of a binding type of "range" when a table object has been inserted inside the bounds of the range.

For example I have a binding on A1:C3, and a table has been inserted in the Excel UI using. Insert -> Table at B2:C3 with My table has headers checked on.

Using the below function to update the raw values of that range seems to remove the table object.

Alternatively, If the binding range is smaller then the table object, ie. binding inside the bounds of the table object, the table and formatting remains.

var bindingId = "123";
var newValue = [["123","123","123"], ["123","123","123"], ["123","123","123"]];

return Excel.run(function (ctx) {

    var binding = ctx.workbook.bindings.getItem(bindingId);
    var range = binding.getRange().load('values, address');

    return ctx.sync().then(function () {

        range.values = newValue;

        return ctx.sync();

    });

});

Marc LaFleur · Accepted Answer · 2017-09-20 21:08:38Z

1

To return a table object from your binding, should use bindingObject.getTable() rather than bindingObject.getRange().

Once you have a table object, you can use the table.rows.add() method to add one or more rows while preserving the existing table.

answered Sep 20, 2017 at 21:08

Marc LaFleur

33.4k4 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Office Excel JS - Change Range values of table object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related