Open Powerpoint file using Excel VBA 2016 from SharePoint

Question

Using VBA in Excel 2010 I could open a PowerPoint file on SharePoint by passing the URL location of the file. If I wasn't logged in it would prompt me for my credentials so it could open it.

However, in Excel 2016, while I get the prompt for opening Excel files on SharePoint, I don't get the prompt when opening PowerPoint files. Instead I just get a Run-time error '-2147467259 (80004005)' meaning not authenticated. If I log into SharePoint first then run the macro it opens, but I don't want that. Any suggestions on how I can bring the prompt back for 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

From what I've looked into on this, the error is related to creating the powerpoint object. I am not sure if it's related to references, but I get error 429 just trying to open an application of powerpoint. — Cyril
In my case, I have modified "dim objPPT As PowerPoint.Application" to "Dim objPPT As Object", then PowerPoint is opened well, but I cannot open the file from the web site. Sorry, that what I only can help — D. O.
It might be difficult to skip the authentication process in SharePoint since each site, list, folder, etc has specific user permissions. Have you tried mapping the document library to a drive letter using the WebDAV address to access the library in your code? Here is a similar question: stackoverflow.com/questions/12217680/… — Part_Time_Nerd
I had an issue with one of our locations before when I tried mapping the drive so I've stayed away from it. Good suggestion though. — pheeper

pheeper pheeper · Accepted Answer · 2017-03-20T16:55:17

I was able to solve this by opening the file, looping through all open PPT documents looking for the specific file name, and then assigning it to an object variable.

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

Open Powerpoint file using Excel VBA 2016 from SharePoint

1 Answers