VBA File Path Issue - Sharepoint

[dw1256]

Hi There,

I am using the following to assign the workbooks path to myPath:

myPath = activeworkbook.path

When I do this, it assigns the sharepoint path:

https://name.sharepoint.com/sites/sitename/Shares/Acme/Global Documents/QPCG

I want the path in this format:

C:\Users\...........\Global Documents\QPCG

Is there an easy way to do this? If not, can I somehow convert it to what I am looking for?

Thanks.

 

[JEC]

Sharepoint paths do not have user specific info. You really need to sync the folder first.
Also, every user could have a different path after syncing because they might have different access rights.

Using filedialog is a safe way of finding the correct path while using shared folders/files.

 

[Alex Blakenburg]

Returns C:\Users\myprofilename

@JEC sounds like he probably has more experience with Sharepoint than I have. I no longer have access to a sharepoint drive and have not seen anyone who has been terribly successful in accessing the path. I was just suggesting that if your full sharepoint path has 2 components a Sharepoint specific part that tends to repeat for all sharepoint folders and a 2nd folder path that is common to sharepoint and the C drive sync folder then you could do a replace of the sharepoint specific part with the Environ("UserProfile") result.

Since I can't see your full sharepoint path nor your full C drive path I can't be more specific than that.

 

[dw1256]

@JEC sounds like he probably has more experience with Sharepoint than I have. I no longer have access to a sharepoint drive and have not seen anyone who has been terribly successful in accessing the path. I was just suggesting that if your full sharepoint path has 2 components a Sharepoint specific part that tends to repeat for all sharepoint folders and a 2nd folder path that is common to sharepoint and the C drive sync folder then you could do a replace of the sharepoint specific part with the Environ("UserProfile") result.

Since I can't see your full sharepoint path nor your full C drive path I can't be more specific than that.

Here are the two paths:

https://acme.sharepoint.com/sites/Shares/Company/Global Documents/QPCG/ClientName (DW)/2022 Q2

C:\Users\ProfileName\acme.com\Shares - Company\Global Documents\QPCG\ClientName (DW)\2022 Q2

Can you offer any suggestions?

 

[Alex Blakenburg]

Not as tidy as I would have hoped for.
A replace would most likely on work if:
• For sharepoint this part was always the same
https://acme.sharepoint.com/sites/Shares/Company/
• For the C Drive that this part was always the same
\acme.com\Shares - Company\

Any chance that would be the case ?

 

[dw1256]

Not as tidy as I would have hoped for.
A replace would most likely on work if:
• For sharepoint this part was always the same
https://acme.sharepoint.com/sites/Shares/Company/
• For the C Drive that this part was always the same
\acme.com\Shares - Company\

Any chance that would be the case ?

Correct. Those will always be the same for the purpose of this macro.

 

[Alex Blakenburg]

Just playing around with the combinations, this might be an option.

Sub GetPath()

    Dim shareptPath As String
    Dim localPath As String
    
    ' In your case this next line would be
    ' shareptPath = ActiveWorkbook.Path
    shareptPath = "https://acme.sharepoint.com/sites/Shares/Company/Global Documents/QPCG/ClientName"
    localPath = Environ("UserProfile") & "\acme.com\Shares - Company\" _
                    & Replace(Right(shareptPath, Len(shareptPath) - InStr(shareptPath, "Company/") - Len("Company/") + 1), "/", "\") _
                    & "\"
                    
    Debug.Print localPath

End Sub

 

[dw1256]

Just playing around with the combinations, this might be an option.

Sub GetPath()

    Dim shareptPath As String
    Dim localPath As String
   
    ' In your case this next line would be
    ' shareptPath = ActiveWorkbook.Path
    shareptPath = "https://acme.sharepoint.com/sites/Shares/Company/Global Documents/QPCG/ClientName"
    localPath = Environ("UserProfile") & "\acme.com\Shares - Company\" _
                    & Replace(Right(shareptPath, Len(shareptPath) - InStr(shareptPath, "Company/") - Len("Company/") + 1), "/", "\") _
                    & "\"
                   
    Debug.Print localPath

End Sub

Perfect! Thank you.

 

[Alex Blakenburg]

I don't think it makes the cut for perfect but if it works for you, we will take it as win

Thanks @JEC for pointing out that the Filedialog function returns the C drive path for the sync'd file.