Paste table with editable cells from Excel to Powerpoint using VBA

Question

I have some VBA that successdfully copies cell data from Excel and pastes into a given powerpoint slide of a given template.

However, with the pasted object I cannot select individual cells and format as necessary. I assume this is to do with how the paste type is working - What would I need to change it to in the VBA code to make it paste so I can manipulate the contents of the cells?

If I copy some cells in Excel manually and then manually paste special "Keep Source Formatting (K)" once in powerpoint, I get an editable table, I can change cell spacing, insert new rows etc. If I can do it manually, surely it can be done using VBA?

Here is my code that does the paste:

'Opens a PowerPoint Document from Excel

Dim PPapp As PowerPoint.Application, PPpres As PowerPoint.Presentation, PPslide As PowerPoint.Slide
Dim XLws As Worksheet

    Set XLws = ActiveSheet
    Set PPapp = New PowerPoint.Application
    Set PPpres = PPapp.Presentations.Open("C:\Users\Colin\Dropbox (Edge45)\Edge45 Team Folder\Edge45 Company Documents\Templates\Powerpoint Templates\Edge45 Audit Template Macro.potm", Untitled:=msoTrue)
    PPapp.Visible = True
    Set PPslide = PPpres.Slides(2)
    XLws.Range("A1:D16").Copy
    PPslide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
    Application.CutCopyMode = False

CLR CLR · Accepted Answer · 2017-01-23T16:18:49

If you change the line:

PPslide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse

to

PPslide.Shapes.PasteSpecial DataType:=ppPasteHTML, Link:=msoFalse

It will insert the range as editable HTML, as if you've copy/pasted the range yourself.

See https://msdn.microsoft.com/en-us/library/office/ff745158.aspx for other options.

Paste table with editable cells from Excel to Powerpoint using VBA

1 Answers