Changing LinkSources in Excel

[LaurenRae7]

I have an end user whose data was migrated into SharePoint from a local server. They have a lot of spreadsheets that reference other spreadsheets to calculate data. So Excel is trying to automatically fix these, but in doing so the link is not correct (it is putting tenant.sharepoint.com at the beginning of the file location. So instead of it being c:\data\example\test1.xlsx it is tenant.sharepoint.com/example/test1.xlsx. However the actual location based on SharePoint would be tenant.sharepoint.com/documents/shared/example/test1.xlsx. I am trying to find a way to have it go through and replace the information. I have found how to get to the $workbook.LinkSources, but can not get it to change the links. I do not know a whole lot about VBA, but am proficient with other programming languages. Any help or guidance would be greatly appreciated. This client has thousands of excel files and a lot of references.

Thanks in advance for any help