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.

 

[SandsB]

I have the links -see first post. The question is how do I convert the Workbooks.Open code
as in
Workbooks.Open ("V:\Reports\DB\" & Range("A8") & "\UReports\" & Range("C8") & " " & Range("B8") & " " & Range("A8") & ".xlsx")

to something that does the same thing in my macro but reference a sharepoint link instaead of a shared drive while using thos same cell references

 

[Alex Blakenburg]

I have not seen done successfully yet so this is a bit of an experiment.
Run it as a stand alone sub to see if it works.

Go to an existing workbook in one of those folder and open it.
Then get the full filename & path using: File > Info > Copy Path
Does that give you a file path starting with http and containing %20 (being space characters) ?

Save that somewhere I put a copy in the same code module with it commented out (prefix of single quote)
This is just so that we can compare the result of "Debug.Print sFullName" character by character to what you saved.

Then using that build your variable sPathBase which is currently your V Drive version.
You have a folder name in that string that you replacing from cell A8. Remove that folder name between the 2 "/" characters with "@@@" as seen in the code.

Sub SharePointWorkOpenVariables()

    Dim wsOpen As Workbook
    
    Dim sPathBase As String
    Dim sPathVariable As String
    Dim sPathFull As String
    Dim sFName As String
    Dim sFullName As String
    
    sPathBase = "V:\Reports\DB\" & "@@@" & "\UReports\"         ' Where "@@@" is a place holder - typically only one @ but want it to be obvious
    sPathVariable = Replace(Range("A8"), " ", "%20")            ' Part of folder name being swapped out
    sPathFull = Replace(sPathBase, "@@@", sPathVariable)        ' Swap out folder name
    sFName = Replace(Range("C8") & " " & Range("B8") & " " & Range("A8") & ".xlsx", " ", "%20")
    sFullName = sPathFull & sFName
    
    ' -------------------
    ' For Initial Testing
    Debug.Print sFullName
    ' -------------------
    
    Set wsOpen = Workbooks.Open(Filename:=sFullName)
End Sub