[VBA] How to Auto-Resize Excel Charts Pasted in PowerPoint (Adjust by cm)

It is common to want to precisely adjust the size of an Excel chart after pasting it into PowerPoint using VBA.

In this article, I will share the VBA code to paste a chart as a “picture” and then automatically resize it to specific dimensions in centimeters (cm).

The Goal

• Paste an Excel chart into PowerPoint using VBA.
• Automatically adjust the chart size to 5cm height and 8cm width.
• Handle the unit conversion, as PowerPoint requires size specification in points (pt) (1cm ≈ 28.35pt).

Environment

• OS: Windows 10 Pro
• Version: 20H2
• Excel / PowerPoint: Microsoft 365

The VBA Code

The following code pastes a chart into a specified template slide and resizes it to your desired dimensions.

Sub ExportGraphToPowerPoint()
    Dim ppApp As Object
    Dim ppPres As Object
    Dim ppSlide As Object
    Dim ChartObj As ChartObject
    Dim pptTemplatePath As String
    Dim newFileName As String
    Dim myShape As Object
    Dim desiredWidth As Single
    Dim desiredHeight As Single

    ' Convert size to points (1cm = approx. 28.35pt)
    desiredWidth = 8 * 28.35   ' Width 8cm
    desiredHeight = 5 * 28.35  ' Height 5cm

    ' Set initial save filename with date
    Dim defaultFileName As String
    defaultFileName = "\Template_" & Format(Now, "yyyymmdd") & ".pptx"

    ' Show file save dialog
    newFileName = Application.GetSaveAsFilename( _
        InitialFileName:=ThisWorkbook.Path & defaultFileName, _
        FileFilter:="PowerPoint Presentation (*.pptx), *.pptx", _
        Title:="Select Save Location")

    If newFileName = "False" Then Exit Sub

    ' Specify relative path for template file
    ' Note: Adjust this path to match your actual template location
    pptTemplatePath = ThisWorkbook.Path & "\..\Template.pptx"

    ' Launch PowerPoint
    Set ppApp = CreateObject("PowerPoint.Application")
    ppApp.Visible = True

    ' Open template presentation
    Set ppPres = ppApp.Presentations.Open(pptTemplatePath)

    ' Copy Excel chart (Name: graph)
    Set ChartObj = ThisWorkbook.Sheets(1).ChartObjects("graph")
    ChartObj.Copy

    ' Paste as picture on Slide 1
    Set ppSlide = ppPres.Slides(1)
    Set myShape = ppSlide.Shapes.Paste

    ' Change size to specified dimensions (Width 8cm x Height 5cm)
    myShape.Width = desiredWidth
    myShape.Height = desiredHeight

    ' Save file
    ppPres.SaveAs newFileName

    ' Release objects
    Set myShape = Nothing
    Set ppSlide = Nothing
    Set ppPres = Nothing
    ppApp.Quit
    Set ppApp = Nothing
End Sub

Key Points for Resizing

' Convert 8cm width and 5cm height to points
desiredWidth = 8 * 28.35
desiredHeight = 5 * 28.35

myShape.Width = desiredWidth
myShape.Height = desiredHeight

This section controls the size in centimeters. Since PowerPoint manages position and size internally in points (pt), you must convert your desired centimeter values using the formula 1cm ≈ 28.35pt.

Code Workflow

1. Select Save Location: The user chooses where to save the new file and what to name it.
2. Open Template: Opens a pre-existing PowerPoint template (.pptx).
3. Copy Chart: Copies a specific chart (named “graph”) from Excel.
4. Paste: Pastes the chart onto the first slide of the PowerPoint presentation.
5. Resize: Changes the pasted chart’s size to 8cm x 5cm.
6. Save & Close: Saves the presentation with the selected name and closes PowerPoint.

Summary

Using VBA, you can accurately adjust object sizes in PowerPoint using centimeter units.

• PowerPoint manages size and position in points (pt).
• Calculate the necessary points based on 1cm ≈ 28.35pt.
• Use myShape.Width and myShape.Height to resize images or charts to your exact specifications.

This technique is directly useful for maintaining design consistency and improving readability when automatically generating presentation materials.