使用SharePoint的Excel VBA 2016打开PowerPoint文件

Question

在excel 2010中使用vba我可以通过传递文件的URL位置在SharePoint上打开PowerPoint文件。如果我不登录它，它会提示我获得我的凭据，以便可以打开它。

但是，在Excel 2016中，当我在SharePoint上打开Excel文件的提示时，打开PowerPoint文件时不会提示。取而代之的是，我只会收到一个运行时错误'-2147467259（80004005）'未经身份验证。如果我首先登录SharePoint，请运行它打开的宏，但我不想要。关于如何将提示恢复到PowerPoint的任何建议？

Sub OpenPowerPointFile()
    Dim objPPT As PowerPoint.Application
    Dim objPres As Object
    Set objPPT = CreateObject("Powerpoint.Application")
    objPPT.Visible = True
    Set objPres = objPPT.Presentations.Open("https://spsite.com/report_template.pptx")
End Sub

Answer 1

我能够通过打开文件来解决此问题，遍及查找特定文件名的所有打开PPT文档，然后将其分配给对象变量。

Private Function openPowerPointPresentationFromURL(filePath As String, fileName As String) As PowerPoint.Presentation
    Dim ppProgram As PowerPoint.Application
    Dim ppCount As Integer
    Dim i As Integer
    On Error GoTo ErrHandler
    ' Open the file 
    ThisWorkbook.FollowHyperlink (filePath)        
    ' Set the object by looping through all open PPT files and look for the passed file name
    Set ppProgram = GetObject(, "PowerPoint.Application")            
    ppCount = ppProgram.Presentations.Count
    For i = 1 To ppCount + 1
        If ppProgram.Presentations.Item(i).Name = fileName Then
            Set openPowerPointPresentationFromURL = ppProgram.Presentations.Item(i)
            Exit Function
        End If
    Next i
    On Error GoTo 0
ErrHandler:
    MsgBox "There was an error opening the PowerPoint template that is required for this report."
    On Error GoTo 0
End Function