exceltypescriptasynchronousoffice-jsoffice-js-helpers

How to Convert String to Range After Awaiting Context.sync?


In the following async function, I am getting the active cell in Excel and then loading the row index. After the context.sync I am creating a string called "newRange". Is there a way that I can set the current range to my "newRange"? I need to convert newRange to a range.

For example, if my Active cell was Sheet1!B8 then my "newRange" would be "Sheet!A8:E8". After finding this, I then need to convert it to a range and load that address and await context.sync() again. So how can I convert "newRange" to a range?

  addToBOM = async () => {
    try {
      await Excel.run(async context => {
        let range = context.workbook.getActiveCell();
        range.load("rowIndex");
        range.format.fill.color = "yellow";
        await context.sync();
        let newRange = (`Sheet1!A${range.rowIndex}:E${range.rowIndex}`);
        console.log(newRange)
      });
    } catch (error) {
      console.error(error);
    }
    this.setState({
    })
  };

Solution

  • You can't literally change the address of your range object because the address property is Read Only. However, you can generate a new range with the desired address by using either the Range.getOffsetRange method or the Range.getResizedRange method.