Copy Data to PowerPoint with Excel VBA

Objective

This code will copy a range of data from Excel and paste it as a table into a new PowerPoint slide.

Steps

1. Create a PowerPoint object.
2. Create a new PowerPoint presentation.
3. Copy data from Excel.
4. Insert the copied data into PowerPoint.

VBA Code

Sub CopyExcelToPowerPoint()
    ' Declare variables for PowerPoint and Excel objects
    Dim pptApp As Object
    Dim pptPresentation As Object
    Dim pptSlide As Object
    Dim pptTable As Object
    Dim excelRange As Range
    Dim i As Integer, j As Integer   
    ' Select the range of data to copy (e.g., A1:C10)
    Set excelRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C10")   
    ' Check if PowerPoint is already open, if not, open it
    On Error Resume Next
    Set pptApp = GetObject(, "PowerPoint.Application")
    If pptApp Is Nothing Then
        Set pptApp = CreateObject("PowerPoint.Application")
    End If
    On Error GoTo 0
    ' Make PowerPoint visible
    pptApp.Visible = True   
    ' Create a new presentation
    Set pptPresentation = pptApp.Presentations.Add   
    ' Add a new slide (e.g., title and content layout)
    Set pptSlide = pptPresentation.Slides.Add(1, ppLayoutText)  
    ' Copy the Excel range
    excelRange.Copy  
    ' Paste the range into PowerPoint as a table
    pptSlide.Shapes.PasteSpecial DataType:=2 ' ppPasteEnhancedMetafile   
    ' Resize and position the table
    With pptSlide.Shapes(pptSlide.Shapes.Count)
        .LockAspectRatio = MsoTriState.msoFalse
        .Left = 100
        .Top = 100
        .Width = 500
        .Height = 300
    End With
End Sub

Explanation of the Code

1. Declaring PowerPoint and Excel Objects:
   • pptApp: Variable for the PowerPoint application.
   • pptPresentation: Variable for the PowerPoint presentation.
   • pptSlide: Variable for a slide in the PowerPoint presentation.
   • pptTable: Variable for the table shape in PowerPoint.
   • excelRange: The range of cells in Excel that you want to copy.
2. Creating or Retrieving the PowerPoint Instance:
   • The code attempts to get a running instance of PowerPoint with GetObject. If PowerPoint is not open, it creates a new instance with CreateObject.
3. Creating a Presentation and a Slide:
   • A new presentation is created with pptApp.Presentations.Add.
   • A slide with a title and content layout (ppLayoutText) is added to the presentation.
4. Copying Data from Excel:
   • The data from the Excel range (e.g., Range(« A1:C10 »)) is copied using the .Copy method.
5. Pasting Data into PowerPoint:
   • The copied data is pasted into PowerPoint using the .PasteSpecial method. The DataType:=2 means the content is pasted as an enhanced metafile (ppPasteEnhancedMetafile), which is effectively an image of the table.
6. Resizing and Positioning the Table in PowerPoint:
   • After pasting, the table is resized and positioned on the slide. The properties .Left, .Top, .Width, and .Height are used to control the table’s position and size on the slide.

Notes

• Adaptability: You can adjust the range of data copied by changing the Range(« A1:C10 ») reference to the desired range.
• Paste Type: You can choose different paste types by modifying the DataType in .PasteSpecial. For example, use DataType:=1 for a regular paste or DataType:=2 for an enhanced metafile paste (which is an image of the table).
• Customization: You can customize the slide appearance, table size, or any other formatting based on your specific needs.

Conclusion

This code allows you to copy data from an Excel sheet and paste it as a table into a PowerPoint slide. You can modify it for different ranges, paste formats, or other adjustments according to your project requirements.