Excel VBA - 在 SharePoint 上查找文件的本地文件位置

Question

我有一个创建了 SharePoint 文档文件夹的企业帐户，我已通过 OneDrive(在 D 盘中，本地访问)将其本地同步到我的计算机。

在 Excel 的 VBA 中，我知道有一种方法可以使用调用 OneDrive 本地位置

environ("OneDrive")

但是，我不知道如何在我的 Excel 文件中指定它以找到存储在 SharePoint 上的本地文件。

这很重要的原因是我需要使用 VBA 来执行邮件合并，并且我发现 SharePoint 存储的 XLSM 无法正确连接。代码如下:

Private Sub InvitationLetter_Click()
Dim WordApp As New Word.Application, ActionFormDocument As Word.Document, WorksheetName As String
Dim OSPFullPath As String: OSPFullPath = ThisWorkbook.FullName

WorksheetName = ActiveWorkbook.Sheets("Guest Speakers").Name

With WordApp
  .DisplayAlerts = wdAlertsNone
  Set ActionFormDocument = .Documents.Open(ThisWorkbook.Path & "\1.2 - Guest Speaker\02 - Guest Speaker Invitation Letter.docx", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentfiles:=False)

  With ActionFormDocument
    With .MailMerge
      .MainDocumentType = wdFormLetters
      .SuppressBlankLines = False
      .OpenDataSource Name:=OSPFullPath, ReadOnly:=False, _
        LinkToSource:=True, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=OSPFullPath;" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
        SQLStatement:="SELECT * FROM`" & WorksheetName & "$`" & "WHERE `Status` = 'Pending' And `Nomination Details Alert` LIKE '%Urgent%'", _
        SubType:=wdMergeSubTypeAccess
      .ViewMailMergeFieldCodes = 0
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
    End With
  End With

  .DisplayAlerts = wdAlertsAll

  .Visible = True
  .Activate
End With

Unload Me
End Sub

我需要将 OSPFullPath 指向本地(例如 D:\One Drive\Excel.xlsm)。我无法使用“全名”功能。

我可以使用任何环境代码，或者无论如何检测文件位置？

Answer 1

如果我正确理解了这个问题，以下内容可能会有所帮助，因为它获得了 sharepoint 上的工作簿的全名:所有功劳在此处回答 3373470

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

在此线程上有更多答案以及指向其他页面的链接可能会有所帮助