Excel's fullname property with OneDrive

Question

If I want to use the open Workbook object to get the fullname of an Excel file after saving it, but that file has been synchronized to OneDrive, I get a "https" address instead of a local one, which other programs cannot interpret.
How do I get the local filename of a file like this?

Example:
Save a file to "C:\Users\user\OneDrive - Company\Documents".
OneDrive does its synchronization.
Querying Workbook.FullName now shows as "https://..."

Answer 1

I found a thread online which contained enough information to put something simple together to solve this. I actually implemented the solution in Ruby, but this is the VBA version:

Option Explicit  Private Function Local_Workbook_Name(ByRef wb As Workbook) 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, wb.FullName, "https://", vbTextCompare) > 0 Then      'Replace forward slashes with back slashes     Local_Workbook_Name = Replace(wb.FullName, "/", "\")      'Get environment path using vbscript     Set objShell = CreateObject("WScript.Shell")     UserProfilePath = objShell.ExpandEnvironmentStrings("%UserProfile%")        'Trim OneDrive designators     For Ctr = 1 To 4        Local_Workbook_Name = Mid(Local_Workbook_Name, InStr(Local_Workbook_Name, "\") + 1)     Next        'Construct the name     Local_Workbook_Name = UserProfilePath & "\OneDrive\" & Local_Workbook_Name    Else      Local_Workbook_Name = wb.FullName    End If  End Function  Private Sub testy()    MsgBox ActiveWorkbook.FullName & vbCrLf & Local_Workbook_Name(ActiveWorkbook)  End Sub 

Answer 2

Horoman's version (2020-03-30) is good because it works on both private and commercial OneDrive. However it crashed on me because the line "LocalFullName = oneDrivePath & Application.PathSeparator & endFilePath" inserts a slash between oneDrivePath & endFilePath. Moreover, one should really try out paths "OneDriveCommercial" and "OneDriveConsumer" before "OneDrive". So here's the code that works for me:

Sub TestLocalFullName()     Debug.Print "URL: " & ActiveWorkbook.FullName     Debug.Print "Local: " & LocalFullName(ActiveWorkbook.FullName)     Debug.Print "Test: " & Dir(LocalFullName(ActiveWorkbook.FullName)) End Sub  Private Function LocalFullName$(ByVal fullPath$)     'Finds local path for a OneDrive file URL, using environment variables of OneDrive     'Reference https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive     'Authors: Philip Swannell 2019-01-14, MatChrupczalski 2019-05-19, Horoman 2020-03-29, P.G.Schild 2020-04-02      Dim ii&     Dim iPos&     Dim oneDrivePath$     Dim endFilePath$      If Left(fullPath, 8) = "https://" Then 'Possibly a OneDrive URL         If InStr(1, fullPath, "my.sharepoint.com") <> 0 Then 'Commercial OneDrive             'For commercial OneDrive, path looks like "https://companyName-my.sharepoint.com/personal/userName_domain_com/Documents" & file.FullName)             'Find "/Documents" in string and replace everything before the end with OneDrive local path             iPos = InStr(1, fullPath, "/Documents") + Len("/Documents") 'find "/Documents" position in file URL             endFilePath = Mid(fullPath, iPos) 'Get the ending file path without pointer in OneDrive. Include leading "/"         Else 'Personal OneDrive             'For personal OneDrive, path looks like "https://d.docs.live.net/d7bbaa#######1/" & file.FullName             'We can get local file path by replacing "https.." up to the 4th slash, with the OneDrive local path obtained from registry             iPos = 8 'Last slash in https://             For ii = 1 To 2                 iPos = InStr(iPos + 1, fullPath, "/") 'find 4th slash             Next ii             endFilePath = Mid(fullPath, iPos) 'Get the ending file path without OneDrive root. Include leading "/"         End If         endFilePath = Replace(endFilePath, "/", Application.PathSeparator) 'Replace forward slashes with back slashes (URL type to Windows type)         For ii = 1 To 3 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name             oneDrivePath = Environ(Choose(ii, "OneDriveCommercial", "OneDriveConsumer", "OneDrive")) 'Check possible local paths. "OneDrive" should be the last one             If 0 < Len(oneDrivePath) Then                 LocalFullName = oneDrivePath & endFilePath                 Exit Function 'Success (i.e. found the correct Environ parameter)             End If         Next ii         'Possibly raise an error here when attempt to convert to a local file name fails - e.g. for "shared with me" files         LocalFullName = vbNullString     Else         LocalFullName = fullPath     End If End Function