VBA Document.Path returns Web path when in OneDrive - Need local path

Question

I am trying to get the local file path to an open document. When I use the Path function I get a web path if the document is in my OneDrive folder. I think the problem is that the file exists in 2 places: C:\Users\myloginname\OneDrive\Documents\Project\Samples AND https://d.docs.live.net/xxxxxxxxxxxx/Documents/Project/Samples

When I try to use the Dir function with the web "path" I get error 52 "Bad file name or number".

How can I get the local path?

Related: https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive — Tim Williams, Jul 27 '21 at 20:30
Thank you Tim. What a thread! In my opinion this is a missing property of the Document object. All the suggested solutions are ugly workarounds. (but I'll use one I'm sure) — mohnston, Jul 27 '21 at 20:50
From the perspective of someone who's supporting a lot of excel macros for a bunch of people slowly migrating files to Sharepoint it's definitely a headache... — Tim Williams, Jul 27 '21 at 20:56
@mohnston, you might be interested in [this solution](https://stackoverflow.com/a/73577057/12287457) for this problem. — GWD, Sep 05 '22 at 22:19

score 0 · Answer 1 · answered Jul 29 '21 at 00:55

The function below will return the local name of a FullName given to it as argument.

Function LocalFullName(ByVal Ffn As String) As String
    ' 294

    ' this is part of the URL address before the file's code name
    ' e.g. https://d.docs.live.net/2abce27df5c02e2f/ ....
    Const DriveID   As String = ".live.net"
    
    Dim Fun()       As String           ' function return value
    Dim n           As Integer          ' index of Fun
    Dim Sp()        As String           ' split array of Ffn
    Dim i           As Integer          ' loop counter: index of Sp
    
    Sp = Split(Ffn, "/")
    For i = 1 To UBound(Sp)
        If InStr(1, Sp(i), DriveID, vbTextCompare) Then Exit For
    Next i
    
    If i > UBound(Sp) Then
        LocalFullName = Ffn
    Else
        ReDim Fun(1 To UBound(Sp))
        For i = i + 2 To UBound(Sp)
            n = n + 1
            Fun(n) = Sp(i)
        Next i
        ReDim Preserve Fun(1 To n)
        LocalFullName = Join(Fun, Application.PathSeparator)
    End If
End Function

If you need to use the name for saving the drive must be added. The snipper below shows how to call the function and add the drive letter.

Sub Snippet()
    ' 294
    
    Const DriveID   As String = "D:\"
    Dim Wb          As Workbook
    
    Set Wb = ThisWorkbook
    Debug.Print DriveID & LocalFullName(Wb.FullName)
End Sub

score 0 · Answer 2 · answered Jul 29 '21 at 04:08

I ended up using this as I only wanted the folder path. Also, it's kind of a choose-your-poison when using hard-coded text, but I worry that ".live.net" might change. Of course, so could "\OneDrive\" so there ya go.

Private Function Local_Workbook_Path(ByRef doc As Document) As String

  Dim Ctr As Long
  Dim objShell As Object
  Dim UserProfilePath As String
  'Check if it looks like a OneDrive location
  If InStr(1, doc.path, "https://", vbTextCompare) > 0 Then

    'Replace forward slashes with back slashes
    Local_Workbook_Path = Replace(doc.path, "/", "\")

    'Get environment path using vbscript
    Set objShell = CreateObject("WScript.Shell")
    UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%")

      'Trim OneDrive designators
    For Ctr = 1 To 4
       Local_Workbook_Path = Mid(Local_Workbook_Path, InStr(Local_Workbook_Path, "\") + 1)
    Next

      'Construct the name
    Local_Workbook_Path = UserProfilePath & "\OneDrive\" & Local_Workbook_Path
    Local_Workbook_Path = Replace(Local_Workbook_Path, "%20", " ")
  Else

    Local_Workbook_Path = doc.path

  End If

End Function

VBA Document.Path returns Web path when in OneDrive - Need local path

2 Answers2

Linked