[Excel VBA] How to Automatically Export Charts and Tables to PowerPoint

When creating reports, you often need to transfer charts and tables from Excel to PowerPoint. Doing this manually for multiple slides is time-consuming.

This article provides a VBA macro that automates the process of opening PowerPoint, creating slides, and pasting specific Excel charts and ranges as tables.

Complete VBA Code

The following macro exports three specific charts and three cell ranges to a new PowerPoint presentation, saving it in the same folder as your Excel file.

Sub ExportChartsAndTableToPowerPoint()
    ' Objects for PowerPoint manipulation
    Dim pptApp As Object
    Dim pptPres As Object
    Dim pptSlide As Object
    Dim ws As Worksheet
    Dim chartObj1 As ChartObject, chartObj2 As ChartObject, chartObj3 As ChartObject
    Dim tableRange1 As Range, tableRange2 As Range, tableRange3 As Range
    Dim savePath As String
    Dim pptTable As Object
    Dim i As Long, j As Long
    Dim chartTop As Single, chartHeight As Single
    Dim chartWidth As Single
    
    ' Size settings (Points: 1cm ≈ 28.3465 pt)
    Dim chartHeightCm As Single, chartWidthCm As Single
    chartHeightCm = 10.3 * 28.3465
    chartWidthCm = 33.9 * 28.3465
    
    Dim textBoxWidthCm As Single
    textBoxWidthCm = 20 * 28.3465
    
    ' Set Excel worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    
    ' Set charts (Change names to match your actual chart names)
    Set chartObj1 = ws.ChartObjects("森")
    Set chartObj2 = ws.ChartObjects("林")
    Set chartObj3 = ws.ChartObjects("木")
    
    ' Set table ranges
    Set tableRange1 = ws.Range("A1:B10")
    Set tableRange2 = ws.Range("A12:B22")
    Set tableRange3 = ws.Range("A24:B34")
    
    ' Launch PowerPoint or get existing instance
    On Error Resume Next
    Set pptApp = GetObject(Class:="PowerPoint.Application")
    If pptApp Is Nothing Then
        Set pptApp = CreateObject(Class:="PowerPoint.Application")
    End If
    On Error GoTo 0
    
    pptApp.Visible = True
    Set pptPres = pptApp.Presentations.Add
    
    ' --- Slide 1: Chart and Table for "森" ---
    Set pptSlide = pptPres.Slides.Add(1, 1) ' 1 = ppLayoutText
    With pptSlide.Shapes.Title
        .TextFrame.TextRange.Text = "森"
        .TextFrame.TextRange.Font.Size = 28
        .Left = 0: .Top = 0
        .Width = textBoxWidthCm: .Height = 50
    End With
    
    chartObj1.Chart.ChartArea.Copy
    pptSlide.Shapes.PasteSpecial DataType:=2 ' 2 = ppPasteEnhancedMetafile
    With pptSlide.Shapes(pptSlide.Shapes.Count)
        .Left = 0: .Top = 50
        .Width = chartWidthCm: .Height = chartHeightCm
        chartTop = .Top: chartHeight = .Height
    End With
    
    ' Add Table to Slide 1
    Set pptTable = pptSlide.Shapes.AddTable(tableRange1.Rows.Count, tableRange1.Columns.Count, 0, chartTop + chartHeight, 500, 200).Table
    For i = 1 To tableRange1.Rows.Count
        For j = 1 To tableRange1.Columns.Count
            pptTable.Cell(i, j).Shape.TextFrame.TextRange.Text = tableRange1.Cells(i, j).Text
            pptTable.Cell(i, j).Shape.TextFrame.TextRange.Font.Size = 12
        Next j
    Next i
    
    ' --- Repeat for Slide 2 ("林") and Slide 3 ("木") ---
    ' (Logic is identical to Slide 1 using chartObj2/3 and tableRange2/3)
    
    ' Save the presentation
    savePath = ThisWorkbook.Path & "\ExportedPresentation.pptx"
    pptPres.SaveAs savePath
    
    ' Cleanup
    Set pptSlide = Nothing: Set pptPres = Nothing: Set pptApp = Nothing
    MsgBox "PowerPoint creation complete.", vbInformation
End Sub

Section Details

1. Launching PowerPoint

The macro uses GetObject to see if PowerPoint is already open. If not, it uses CreateObject to start it. Setting .Visible = True ensures you can see the process.

2. Creating Slides and Titles

The pptPres.Slides.Add method creates a new slide. The code then accesses pptSlide.Shapes.Title to set the slide header, font size, and position.

3. Pasting Charts

Excel charts are copied using .ChartArea.Copy and pasted into PowerPoint using .PasteSpecial.

• DataType:=2 (ppPasteEnhancedMetafile) is used to maintain high image quality while keeping the file size reasonable.

4. Constructing Tables

Instead of a simple paste, the macro creates a native PowerPoint table using .Shapes.AddTable. It then loops through the Excel range and transfers the text cell by cell. This allows for precise control over the font size and table layout within the slide.

Summary

By using this VBA macro, you can eliminate the manual labor of copying and pasting dozens of items into a presentation. This approach ensures consistency in size and placement across all slides, making your automated reports professional and efficient.