Updating a PowerPoint via Excel VBA and save the *.pptx file (not SaveAs)

Question

The aim is, that I have both (Excel and PowerPoint) open simultaneously. So when I run my Excel macro the PowerPoint should be updated and saved in the same file - no reopening and then some kind of SaveAs...

I know / suppose that the solution of my question lies in Set oPresentation = appPPT.Presentations.Open(sPPTfile, msoFalse) - the Presentations.Open-method. But I have no clue how to overcome this issue. I found nothing appropriate.

So here my code and many thanks for your hints and comments in advance.

Sub openPPT()
   
    Dim appPPT As PowerPoint.Application
    Dim slide As PowerPoint.Slide
    Dim oPresentation As PowerPoint.Presentation
    Dim txtFeld(12) As PowerPoint.Shape
    Dim sPPTfile As String
    Dim wkb As Workbook
    Dim wks As Worksheet
    
    On Error GoTo err
    
    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets(1) 
    Set appPPT = New PowerPoint.Application 


    sPPTfile = "C:\Users\xxx\TestPowerPoint.pptx" 
    appPPT.Visible = True
    ' here is the Presentations.Open method which causes probably my issue
    Set oPresentation = appPPT.Presentations.Open(sPPTfile, msoFalse)
    Set slide = appPPT.ActivePresentation.Slides(3)
    slide.Shapes("Rectangle 32").TextFrame.TextRange.Text = Tabelle1.Range("E10")
    ' in the following two lines of code an error message arises in Excel
    oPresentation.Save 
    ' appPPT.ActivePresentation.Save  ' ed2 suggested this line of code
   

    err:
    If err.Number <> 0 Then
        MsgBox err.Number & vbCrLf & err.Description
    End If

    Set slide = Nothing: Set appPPT = Nothing: Set oPresentation = Nothing

End Sub

Edit_1: The error message

The following error message arises when using oPresentation.Save or appPPT.ActivePresentation.Save (which ed2 suggested).

Here on this forum a user is also questioning of this "Err.Number = -2147467259" but this error message doesn't exist in the recommended list... Perhaps that's also a hint?!

Edit_2: When using the proposed code of FunThomas also an error message arises. This time not in Excel, but in Excel VBA (see screenshot) and it is caused by oPresentation.Save.

Edit_3 and solution: The issue why also FunThomas code is failing is caused by the file naming.

This code of line If oPresentation.FullName = sPPTfile Then was never True because

• oPresentation.FullName is https://xxx/TestPowerPoint.pptx and
• sPPTfile is C:\Users\xxx\TestPowerPoint.pptx

So when you

• copy your file path out of the explorer you get C:\Users\xxx\TestPowerPoint.pptx
• right click on the file (in the explorer) and choose copy link you get some insane https://xxx/... stuff
• run the code and extract the correct name out of oPresentation.FullName. This needs some extra preparation... but it's worth ;-)

Answer 1

As far as I understand, you are executing this code multiple times. Thing is that you open a new Powerpoint instance every time (Look to the windows task manager), and in every instance you open the presentation another time. The first time the presentation is opened (no matter if via code or directly), it can be modified. All other instances will open the presentation in read only mode. If you modify it, you will need to save it with another name (create a copy).

So what you have to do is to check if PowerPoint and the presentation is already open. There is an easy way to do so, however it has the limitation that it works only with one PowerPoint instance. This should be sufficient for most cases.

Sub openPPT()    
    Const sPPTfile = "C:\Users\xxx\TestPowerPoint.pptx"
    Dim appPPT As PowerPoint.Application
    
    On Error Resume Next
    Set appPPT = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    If appPPT Is Nothing Then
        ' Powerpoint is not yet open.
        Set appPPT = New PowerPoint.Application
    End If
    
    ' Search if presentation is already open
    Dim oPresentation As PowerPoint.Presentation, found As Boolean
    For Each oPresentation In appPPT.Presentations
        If oPresentation.FullName = sPPTfile Then
            found = True
            Exit For
        End If
    Next
    If Not found Then Set oPresentation = appPPT.Presentations.Open(sPPTfile)
    appPPT.Visible = True
    
    (... Now do whatever you want with the presentation...)

    oPresentation.Save
End Sub

(Note: Before you test this code the first time, be sure that you have closed all Powerpoint Instances. If you have hidden instances running, close them with the task manager).

If you really want to deal with several instances, things get more complicated, you will need to find all PowerPoint instances and loop over all open presentations of all those instances. If you are interested, have a look to Having multiple Excel instances launched, how can I get the Application Object for all of them? (where the same thing is done for Excel) or http://exceldevelopmentplatform.blogspot.com/2019/01/vba-code-to-get-excel-word-powerpoint.html