Unanswered

Using Script to copy to multiple files in a folder

(0) Share

Report

Posted on by MFM0870

Sorry if this is the wrong forum I got redirected to post my question on Scripts here by the Excel team

I have a large number of files for individual clients that periodically need to be updated with the same new content. I am trying to create a script which loops through and opens each file within a specific folder and copies the content from a masterfile into each file held in the folder.

The files are saved in a sharepoint folder.

I am getting the following error

See line 8, column 7: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

This is the line of code I have used

let folder: ExcelScript.DirectoryObject = workbook.getDirectoryObjectFromUrl(folderUrl);

Categories:

General topics

All responses (3)

Answers (0)

Sort by

MichelleRanMSFT Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MFM0870, thanks for your question! If you're writing an Office Script, then one thing you should be aware of is that a script can only modify the workbook that it's being run in - it can't modify multiple files. To do what you're describing, you'd probably want to use an Office Script together with a Power Automate flow. Something along the lines of: list files in folder -> apply to each -> run script on each file. If you can describe your scenario in more detail, I'd be happy to give more guidance on how to set up such a flow.
MFM0870 15 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Michelle
I have around 90 files that have an identical format. I would like to copy the same data from a range of cells in a masterfile to each file. If Power Automate can open each file that would be great.
MichelleRanMSFT Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
You can try a flow like this (derived from the "Run an Office Script on every Excel workbook in a SharePoint folder" template):

This is the first script to get the range from the masterfile:
function main(workbook: ExcelScript.Workbook, worksheet: string, rangeToCopy: string) { return JSON.stringify(workbook.getWorksheet(worksheet).getRange(rangeToCopy).getValues()); }
This is the second script to copy the range:
function main(workbook: ExcelScript.Workbook, rangeValues: string, worksheet: string, rangeToCopyTo: string) { let values: (string | number | boolean)[][] = JSON.parse(rangeValues); workbook.getWorksheet(worksheet).getRange(rangeToCopyTo).setValues(values); }
Let me know if that helps!