How to force formula reference to use the current workbook for inserted worksheets via insertWorksheetsFromBase64 api

Zoe 0 Reputation points
2023-03-29T07:19:00.1966667+00:00

When inserting a workbook via insertWorksheetsFromBase64 the formula retains the reference from the source workbook instead of using the current workbook.

For example I have the formula below from the source workbook =Unit_Mix[[#Totals],[Market Rent]]

but the worksheet containing the formula above was inserted via insertWorksheetsFromBase64 it ends up having an external reference to another workbook which I don't know where this file came from ='55D8FE051'!Unit_Mix[[#Totals],[Market Rent]]

I haven't tried anything yet because of limited capibility of the API. One idea I had is to iterate all cells containing formula and replace all formula that contains an external reference but I'd like to explore other options.

What I really want is, when a workbook was inserted via insertWorksheetsFromBase64 it should drop any external reference or add an option to drop external reference.

JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
974 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.