1

I need to run an excel-macro (copies and pastes w/in same workbook) on the same day each month indefinitely. The workbook resides on SharePoint 2019 and I do not have any external machines to run a cron.

From my understanding I can use/script an office.js API that will allow me to run macros on SharePoints web-based excel GUI. However, I do not understand/can't find any information on how to automate/schedule the macro to run at a specific time.

I did see this In JavaScript, how can I have a function run at a specific time? and this but I'm not sure that would work/would be the best method.

Flow would be great, and I saw there is an add-in https://flow.microsoft.com/en-us/blog/introducing-microsoft-flow-integration-in-excel/ but it doesn't look like you can "set a time" for it to run. I would also imagine there could be long term issues in that its ability to run is based of user permissions.

In the case you have a better method of achieving what I am looking I'm all ears as well.

Matthew Dawson
  • 11
  • 3

1 Answers1

0

An Office Add-in can only work in a workbook that is opened, and there isn't any way within an add-in to open a workbook at a specified time. If you can find a way to get the workbook to open at a specified time (on someone's computer), then you can have a task pane add-in that checks the date and makes the change. (This code should be in the Office.initialize method or the Office.onReady method. See Initializing your add-in.) You can then configure the add-in to autoopen whenever the workbook is opened. (See Automatically open a task pane with a document.)

A downside of this is that the task pane will open whenever the workbook is opened. That's why the code should first check the date and then check to see if the change has already been made. Users might be puzzled by the task pane. At any rate, they'd have to close it if they didn't want it visible while working with the workbook.

Rick Kirkham
  • 9,038
  • 1
  • 14
  • 32
  • Is it possible to drop a script on the SharePoint server that would "open" the file? Fairly clueless what type of underlying OS a SharePoint server runs on/what language to use. Also, not sure if we own the servers or rent from Microsoft. – Matthew Dawson Jul 10 '19 at 13:13
  • I think that using an Office add-in is probably not the best way to accomplish your scenario. I recommend that you look into using the [Excel REST APIs](https://learn.microsoft.com/en-us/sharepoint/dev/general-development/excel-services-rest-api) in the context of a SharePoint solution. Maybe you could ask a new question and tag it with `SharePoint`. – Rick Kirkham Jul 10 '19 at 17:43