Excel的全名属性与OneDrive [英] Excel's fullname property with OneDrive

问题描述

如果我想使用打开的工作簿对象来保存Excel文件的完整名称，但是该文件已经同步到OneDrive，则我得到一个https地址而不是本地文件，其他程序不能解释。

如何获取这样的文件的本地文件名？

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?

示例：

将文件保存到C：\Users\user\OneDrive - Company\Documents。

OneDrive执行同步。

查询Workbook.FullName现在显示为https：// ...

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

推荐答案

我发现一个线程在线，其中包含足够的信息，使一些简单的一起来解决这个问题。我实际上在Ruby中实现了这个解决方案，但这是VBA版本：

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

这篇关于Excel的全名属性与OneDrive的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！