Running Macros and opening Sharepoint Files locally

Help Studio
,
1

Hello,

What is the best way to open a sharepoint excel file locally and run its macro for unattended processes?

I have seen some activities that look like StudioX activities such as excel.activities.business

We currently are using excel process scope and face a synhcronization issue, the excel data is not updated as fast as we would need it and we cannot run the macro with out of date data

What is the best practice in this case?

2

Hi @adext01

For unattended processes, I would recommend downloading the SharePoint Excel file to a local/temporary folder first.

Then use Excel Process Scope to open it, refresh the data, wait for the refresh to complete, and finally run the macro.

This is more reliable than running the macro directly on the SharePoint file and helps avoid synchronization issues.

3

Hi @adext01 ,
Best Practice for Unattended Automation

  1. Use Microsoft 365 / SharePoint activities to download the Excel file locally from SharePoint before processing.
  2. Open the downloaded file with Excel Application Scope (desktop Excel), not Workbook activities, since macros require Excel to be installed.
  3. Refresh/save any required data first.
  4. Run the macro using Execute Macro activity.
  5. Save and close the workbook.
  6. Upload the updated file back to SharePoint if needed.

To avoid synchronization issues

  • Download a fresh copy from SharePoint immediately before running the macro.
  • If multiple users/processes update the file, implement a file lock/check-in/check-out mechanism or process the file during dedicated time windows.
  • Add a short validation step to confirm the latest data is present before executing the macro.

Regarding Excel.Activities.Business

  • These StudioX activities are useful for business users, but for unattended macro execution, Excel Application Scope + Execute Macro is generally the most reliable and commonly used approach.

Thanks