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

问题描述

如果我想在保存后使用打开的Workbook对象获取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:UsersuserOneDrive - CompanyDocuments".
OneDrive 进行同步.
查询 Workbook.FullName 现在显示为https://..."

Example:
Save a file to "C:UsersuserOneDrive - CompanyDocuments".
OneDrive does its synchronization.
Querying Workbook.FullName now shows as "https://..."

推荐答案

我已经调整了其他人提供的功能以解决一些额外的限制:

I have adjusted the function provided by others to account for some additional additional constraints:

• 当您通过团队网站共享文件时，它不是my.sharepoint.com/"；但是sharepoint.com/"您应该使用它来确定它是否是商业版本.

• When you share files via a team site, it's not "my.sharepoint.com/" but "sharepoint.com/" that you should use to determine if it's a commercial version.

最好计算斜线而不是使用/Documents"的位置因为，例如在法语中，文档文件夹被称为文档部分".商业用途最好算4个斜杠，个人用途最好算2个斜杠.

It is better to count the slashes rather than using the position of "/Documents" because, for example in French, the document folder is called "Documents partages". It is preferable to count 4 slashes for commercial use and 2 slashes for personal use.

如果作为 OneDrive 快捷方式添加的 SharePoint 文件夹不在根目录下，则硬盘驱动器上的本地地址不包含 SharePoint 上的父文件夹.

If the SharePoint folder added as a shortcut to OneDrive is not at the root, the local address on the hard drive does not contain parent folders on the SharePoint.

这是将我的更改考虑在内的代码:

Here is the code that takes my changes into account:

Public Function AdresseLocal$(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$
    Dim NbSlash
    
    If Left(fullPath, 8) = "https://" Then
        If InStr(1, fullPath, "sharepoint.com/") <> 0 Then 'Commercial OneDrive
            NbSlash = 4
        Else 'Personal OneDrive
            NbSlash = 2
        End If
        iPos = 8 'Last slash in https://
        For ii = 1 To NbSlash
            iPos = InStr(iPos + 1, fullPath, "/")
        Next ii
        endFilePath = Mid(fullPath, iPos)
        endFilePath = Replace(endFilePath, "/", Application.PathSeparator)
        For ii = 1 To 3
            oneDrivePath = Environ(Choose(ii, "OneDriveCommercial", "OneDriveConsumer", "OneDrive"))
            If 0 < Len(oneDrivePath) Then Exit For
        Next ii
        AdresseLocal = oneDrivePath & endFilePath
        While Len(Dir(AdresseLocal, vbDirectory)) = 0 And InStr(2, endFilePath, Application.PathSeparator) > 0
            endFilePath = Mid(endFilePath, InStr(2, endFilePath, Application.PathSeparator))
            AdresseLocal = oneDrivePath & endFilePath
        Wend
    Else
        AdresseLocal = fullPath
    End If
End Function

...建立在不同贡献者的工作之上.

...which builds on the work of the different contributors.

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