Excel's fullname property with OneDrive

问题:

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://..."

回答1:

It's possible to improve on Virtuoso's answer to reduce (though not eliminate) the chance that the function returns a "wrong" file location. The problem is that there are various URLs that a workbook's .FullName can be. These are three I'm aware of:

1. A URL associated with the user's OneDrive
2. A URL associated with the user's OneDrive for Business
3. A URL associated with somebody else's OneDrive in the case that that other person has "shared" the file (in which case you open the file via File > Open > Shared with me)

On my PC I can get the relevant local folders to map the first two URLs via the OneDriveConsumer and OneDriveCommercial environment variables, that exist in addition to the OneDrive environment variable, so the code below makes use of these. I'm not aware that it's possible to handle the "Shared with Me" files and the code below will return their https://-style location.

Private Function Local_Workbook_Name(ByRef wb As Workbook) As String

    Dim i As Long, j As Long
    Dim OneDrivePath As String
    Dim ShortName 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
        ShortName = Replace(wb.FullName, "/", "\")

        'Remove the first four backslashes
        For i = 1 To 4
            ShortName = Mid(ShortName, InStr(ShortName, "\") + 1)
        Next

        'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name
        For j = 1 To 3
            OneDrivePath = Environ(Choose(j, "OneDrive", "OneDriveCommercial", "OneDriveConsumer"))
            If Len(OneDrivePath) > 0 Then
                Local_Workbook_Name = OneDrivePath & "\" & ShortName
                If Dir(Local_Workbook_Name) <> "" Then
                    Exit Function
                End If
            End If
        Next j
        'Possibly raise an error here when attempt to convert to a local file name fails - e.g. for "shared with me" files
    End If

    Local_Workbook_Name = wb.FullName

End Function

Unfortunately, if files exist with identical paths within both the OneDrive folder and the OneDrive for Business folder, then the code can't distinguish between them, and may return the "wrong one". I don't have a solution for that.

回答2:

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

回答3:

Very helpful, thanks. I had a similar issue, but with a folder name rather than a filename. Consequently I modified it slightly. I made it work for folder names AND filenames (doesn't have to be a workbook). In case it's helpful, code is below:

Public Function Local_Name(theName As String) As String
    Dim i               As Integer
    Dim objShell        As Object
    Dim UserProfilePath As String

    ' Check if it looks like a OneDrive location.
    If InStr(1, theName, "https://", vbTextCompare) > 0 Then

        ' Replace forward slashes with back slashes.
        Local_Name = Replace(theName, "/", "\")

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

        ' Trim OneDrive designators.
        For i = 1 To 4
            Local_Name = Mid(Local_Name, InStr(Local_Name, "\") + 1)
        Next i

        ' Construct the name.
        Local_Name = UserProfilePath & "\OneDrive\" & Local_Name
    Else
        ' (must already be local).
        Local_Name = theName
    End If
End Function

回答4:

I have the same problem as you. But I have solved that problem. The first I turn off OneDrive before I running the script.

you can add this script on the first script into your vba/module:

Call Shell("cmd.exe /S /C" & "%LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /shutdown")

and then, on your last script on your vba/module you can insert this for activate your OneDrive:

Call Shell("cmd.exe /S /C" & "start %LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /background")

I am using Windows10 on that script.