title: Best way to paste table from Excel to PowerPoint (keeping source formatting)
body: <p>I currently am building a table in Excel through automation in PowerShell. This steps works great, the table ends up exactly as I like. I would now like to paste this in to a PowerPoint presentation.</p>
  
  <p>The PowerPoint presentation is a template I have created, which is then filled in with other elements. I think I have every part cracked apart from this one.</p>
  
  <p>I want to paste from the Excel file that is already open in the background. So far it is activated, and desired range selected. It is then pasted in to the PowerPoint window. However, it comes through as a grey table with none of the formatting.</p>
  
  <p>Previously when putting together my template and manually testing the different components, the line below did the paste from Excel and it was perfect. </p>
  
  <pre><code>ActivePresentation.Application.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
  </code></pre>
  
  <p>However, since moving to the automation (and interacting with different windows etc) it no longer works. Instead giving a "cannot create activex component" error.</p>
  
  <p>Full code below:</p>
  
  <pre><code>Function CreateFLUTemplate(templateFile As String, PresPath As Variant, TalkingPointsDoc As Variant, LineOfBusiness As String, PolicyLink As String)
  
  ' Declare variables to be used
  Dim PPApp As PowerPoint.Application
  Dim PPPres As PowerPoint.Presentation
  Dim WordApp As Word.Application
  Dim PPFile As Object, WordDoc As Object
  Dim TitleBox As PowerPoint.Shape, MetricsHeader As PowerPoint.Shape, MetricsTable As PowerPoint.Shape, PhishingHeader As PowerPoint.Shape, PhishingTable As PowerPoint.Shape
  Dim PolicyHeader As PowerPoint.Shape, PolicyBox As PowerPoint.Shape, TalkingPointsHeader As PowerPoint.Shape, TalkingPointsBox As PowerPoint.Shape, shp As PowerPoint.Shape
  Dim PPSlide As Slide
  Dim WAIT As Double
  Dim ShapeArray As Variant, LabelsArray As Variant, DateLabel As Variant
  Dim i As Integer
  
  ' Open blank presentation file to be updated
  Set PPApp = CreateObject("PowerPoint.Application")
  PPApp.Visible = msoTrue
  Set PPFile = PPApp.Presentations.Open(PresPath)
  Set PPPres = PPApp.ActivePresentation
  
  ' Construct date that will be used in the header sections
  DateLabel = Format(DateSerial(Year(Date), Month(Date), 0), "d mmmm yyyy")
  
  ' Set slide object so we can set our shape variables etc
  Set PPSlide = PPPres.Slides(1)
  
  ' Copy finished Excel table
  
  ' Activate Spreadsheet with table to be copied
  Windows(templateFile).Activate
  Range("A1:E10").Copy
  
  PPApp.Windows(1).Activate
  ' Paste Excel table in to PowerPoint
  'ActivePresentation.Application.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"
  'PPPres.Slides(1).Shapes.PasteSpecial(DataType:=ppPasteShape).Select
  PPApp.ActivePresentation.Slides(1).Shapes.Paste
  
  ' Introduce delay to let paste action happen before moving on
  WAIT = Timer
  While Timer &lt; WAIT + 0.5
     DoEvents
  Wend
  
  ' Take pasted table and save to object
  If PPApp.ActiveWindow.Selection.Type = ppSelectionNone Then
      MsgBox "Nothing is selected", vbExclamation
  Else
      For Each shp In PPApp.ActiveWindow.Selection.ShapeRange
          Set MetricsTable = PPApp.ActivePresentation.Slides(1).Shapes(shp.Name)
      Next shp
  End If
  
  ' Reposition and resize pasted table.
  With MetricsTable
      .Left = 27
      .Top = 108
      .Width = 363
      .Table.Columns(1).Width = 148
      .Table.Columns(2).Width = 28
      .Table.Columns(3).Width = 28
      .Table.Columns(4).Width = 28
      .Table.Columns(5).Width = 131
      .Height = 227
  End With
  </code></pre>