Move from shared drive to Sharepoint - killed my macro

[SandsB]

The IT gods moved all my files from a shared drive to sharepoint. The links within the sheets are fine but whatever magic conversion was going on didn't do anything for my macros. This leaves me stumped.

When the file was on a shared drive.....
3 cells have values that are part of a filename to open. There are several dozen links like this in the macro but they all are some version of this.
A8 = 2024
B8 = Oct
C8 = M3

The line below worked in the macro when the input file was on a shared drive to open file V:\Reports\DB\2024\UReports\M3 Oct 2024.xlsx
Workbooks.Open ("V:\Reports\DB\" & Range("A8") & "\UReports\" & Range("C8") & " " & Range("B8") & " " & Range("A8") & ".xlsx")


I need to open the file using the same 3 cells in this example. If I manually link a cell (B5) to the file, sharepoint tells me this is the link:
='https://coname-my.sharepoint.com/personal/user_coname_com/Documents/Metrics/DB/2024/UReports/[M3 Oct 2024.xlsx]10-23'!$B$5

How do I get excel to link with this http reference and use the 3 cells as input? Is it even possible to link to a sharepoint file?

 

[ExcelToDAX]

You can sync the SharePoint folder to your File Explorer so that you can keep it the same way.

Sync SharePoint and Teams files with your computer - Microsoft Support

Learn how to sync SharePoint files with the new OneDrive sync client.

support.microsoft.com

 

[SandsB]

How do I do that so the macros convert?

 

[Bond00]

you have to go into the macros and change their file path to where the synced data is, so like %userprofile%\YourCompanyName

You could make it something like p = ThisWorkbook.path & "\Folder names\"
or just put the full paths in starting from C: drive

 

[SandsB]

Yeah - it's the syntax to do that where I'm lost.

 

[Skyybot]

Open the Workbook from the SharePoint. Open the VBA Project Explorer. Double click on ThisWorkbook. Type Debug.Print ThisWorkbook.Path in the Immediate Window. That's what you will have to replace the old drive paths with.