Excel requirement - refresh data

Help Studio
1

Hi Everyone,

We have a requirement validate and Excel report which is linked to 20 other files(created monthly) from where the data is flowing to the main report. The business wants to validate if all the files are correctly linked to the main report, if not, then link it.

All the files are stored in SharePoint. Please let me know if there is a way to achieve this.

Forum Engagement Daily Reports
2

You can try this using a VBA macro quickly check if all external links in the main report are valid or broken and verify whether the linked file paths/formulas are correct.

For auto-correcting the links, if the file name or path has changed, then some extra logic is needed (like matching the new file name or using a naming rule).

Overall, yes, a VBA macro can validate the links, and UiPath can open the report, run the macro, and capture the results or even help fix the links based on the logic you provide.

1 Like
3

Hi @Mohammed_Shabaz

Use Microsoft 365 Excel + SharePoint activities to:

  1. Download the main Excel and monthly files from SharePoint
  2. Read formulas / external links in the main report (Get Workbook Links or read formulas via Excel activities)
  3. Validate link paths against the expected SharePoint file URLs
  4. Update broken links using Change Workbook Links (or rewrite formulas if needed)
  5. Save and upload the corrected file back to SharePoint

Step to achive

  • Use Execute Macro / Invoke VBA
  • VBA methods:
    • ActiveWorkbook.LinkSources(xlExcelLinks)
    • ActiveWorkbook.ChangeLink

This is the most reliable way to:

  • List all linked files
  • Validate paths
  • Update broken SharePoint links
4

Hi @Mohammed_Shabaz

Use SharePoint activities to access the main report and source files, read the links in Excel, check if each file exists, and update any broken links using Excel activities. Loop through all files to automate validation and correction.

5

Tried invoking vba, its working. Thanks for all the suggestions.

6

Great @Mohammed_Shabaz , Don’t forget to mark as a solution if you found it helpful.

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.