Search code examples
excelvbatemplatespowerpointpresentation

Embedded OLEO-Object overwrites itself when edited


Im currently trying to do the following steps:

  1. Have a PowerPoint-Template Embedded Into Excel
  2. Run A finished macro that edits the PP-Template
  3. Save the newly edited PP as a new PP-File
  4. Be able to repeat steps 1-3 if wanted

To do this I have embedded the PP-Template as an OLEO-Object (via Insert-->Object-->Choose PPTX) named "PPtemplate" on a Worksheet called "PP Export" and run the following code:

'Dim oEmbFile As Object
'Application.DisplayAlerts = False
'Set oEmbFile = ThisWorkbook.Sheets("PP Export").OLEObjects("PPtemplate")
'oEmbFile.Verb Verb:=xlOpen
'Set oEmbFile = Nothing
'Set PPpres = PPapp.ActivePresentation
'Application.DisplayAlerts = True

This works very well, except I can only do this once. This is because once the first macro edits the template, it automatically saves these changes and the template is not the template anymore but rather the result of the first macro run (edited template)...

How can I stop it from overriding itsself? Maybe somehow extract the template to the User-PC then open and edit it, so the embedded template is always the same even after you run the macro?

Any help/tips would be greatly appreciated! Thanks and stay healthy

EDIT - Solution:

  1. Asking for Saving Place
  2. Save copy of embedded
  3. open copy to edit copy instead of embedded file
With Application.FileDialog(msoFileDialogFolderPicker)
   .AllowMultiSelect = False
    .Show
     PPdateipfad = .SelectedItems.Item(1)
End With

Dim oEmbFile As Object
Application.DisplayAlerts = False
Set oEmbFile = ThisWorkbook.Sheets("PP Export").OLEObjects("PPvorlage")
oEmbFile.Verb Verb:=xlOpen
Set oEmbFile = Nothing
Set PPpres = PPapp.ActivePresentation
Application.DisplayAlerts = True
PPpres.SaveAs (PPdateipfad + "\QM-Check Präsentation.pptx")
PPpres.Close
Set PPpres = PPapp.Presentations.Open(PPdateipfad + "\QM-Check Präsentation.pptx")

Solution

  • Solution:

    1. Asking for Saving Place
    2. Save copy of embedded
    3. open copy to edit copy instead of embedded file
    With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
        .Show
         PPdateipfad = .SelectedItems.Item(1)
    End With
    
    Dim oEmbFile As Object
    Application.DisplayAlerts = False
    Set oEmbFile = ThisWorkbook.Sheets("PP Export").OLEObjects("PPvorlage")
    oEmbFile.Verb Verb:=xlOpen
    Set oEmbFile = Nothing
    Set PPpres = PPapp.ActivePresentation
    Application.DisplayAlerts = True
    PPpres.SaveAs (PPdateipfad + "\QM-Check Präsentation.pptx")
    PPpres.Close
    Set PPpres = PPapp.Presentations.Open(PPdateipfad + "\QM-Check Präsentation.pptx")