Excel 的全名属性与 OneDrive

Question

如果我想在保存后使用打开的 Workbook 对象获取 Excel 文件的全名，但该文件已同步到 OneDrive，我会得到一个“https”地址，而不是其他程序无法解释的本地地址。
如何获取这样的文件的本地文件名？

例子:
将文件保存到“C:\Users\user\OneDrive - Company\Documents”。
OneDrive 进行同步。
查询 Workbook.FullName 现在显示为“https://...”

Answer 1

我在网上找到了一个帖子，其中包含足够的信息，可以将一些简单的东西放在一起来解决这个问题。我实际上在 Ruby 中实现了该解决方案，但这是 VBA 版本:

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