Is there a way to get data from another excel workbook using office script

Ansh Vidyabhanu 40

I have a workbook say 'Source.xlsx' and another workbook say 'Target.xlsx'.

Now I have a cell in 'Target.xlsx' which contains the link for 'Source.xlsx' now i want a button which gets the data from the workbook whose link was in that cell.

just a copy paste operation not a live link.

is this possible to do using a office script or an formula ?

1 answer

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-10-30T18:26:17.76+00:00

@Ansh Vidyabhanu Please take a look at this article and see if that does what you wanted to achieve: https://support.microsoft.com/en-us/office/create-workbook-links-c98d1803-dd75-4668-ac6a-d7cca2a9b95f#ID0EBF=Web.

Please give it a try and let me know how it goes!
Please sign in to rate this answer.
Ansh Vidyabhanu 40 Reputation points

2023-10-31T09:41:03.9566667+00:00

Yes this is what i want to achieve but using excel Office script, as there are lot of workbook links I would like to automate the process and copy the data and then break the links.

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-11-02T19:21:55.8533333+00:00

Ah sorry I overlooked what you said about "not a live link".

I was thinking something like this:

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let range = selectedSheet.getRange("A1"); range.setFormulaLocal("='https://company-my.sharepoint.com/personal/someone_company_com/Documents/[Source Workbook.xlsx]New Sheet'!$A$1"); range.copyFrom(range, ExcelScript.RangeCopyType.values); }

... which would start by pulling in data from an external workbook by creating a formula link, then convert the linked cells into actual cells by using the copyFrom API.

However, since any operations involving pulling external data would pose potential data exfiltration risks (https://support.microsoft.com/en-us/office/block-or-unblock-external-content-in-office-documents-10204ae0-0621-411f-b0d6-575b0847a795#ID0EBF=Web), the setFormulaLocal API will be blocked by default unless you have provided explicit consent to this workbook before running the script.

Michelle Ran 341 Reputation points Microsoft Employee

2023-11-02T21:29:33.9766667+00:00

Adding onto Yutao's answer - if you'd like to break the workbook links after you've copied the data, you can do that with the following method:

workbook.breakAllLinksToLinkedWorkbooks();

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2023-11-02T21:43:03.95+00:00

I think @Michelle Ran 's solution would be much better/simpler especially when you have a bunch links in the workbook.

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let range = selectedSheet.getRange("A1"); range.setFormulaLocal("='https://company-my.sharepoint.com/personal/someone_company_com/Documents/[Source Workbook.xlsx]New Sheet'!$A$1"); workbook.breakAllLinksToLinkedWorkbooks(); }

But the external data connection consent seems be something that one has to live with...
Sign in to comment

Share via

Is there a way to get data from another excel workbook using office script

1 answer