Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Using Excel with Other Office Applications

General Tips on Application Interactions

Copy - Paste Methodology

When you copy from one Microsoft Office application into another, the default Copy-Paste sequence embeds an object of the source application into the target application's document. Generally this is helpful, because it maintains a link between the pasted object and the program used to edit the embedded object. Copied PowerPoint and Word objects are generally small pieces of a PowerPoint or Word document, so the target application's document remains a reasonable size.

Copying an Excel object is different. Because of possible links within an Excel workbook (formulas and chart source data), the designers of Excel chose to copy the entire source workbook and paste it into the target application's document. This behavior has two important implications:

  • If you paste five charts from a 1 MB workbook into a PowerPoint presentation, you are adding 5 MB to the size of the presentation.
  • If you are send the presentation to someone else, you may inadvertently send proprietary information which exists elsewhere in the workbook.

This behavior also makes formatting the container of the embedded object an adventure. For example, changing the size of the container with the intent of resizing the chart it holds may move the chart partly out of the container's view, allowing some of the worksheet to show. Or attempting to change the size of a worksheet view may have curious effects on how much of the worksheet is shown.

When I copy an Excel chart into another application, I always copy the chart as a picture. I copy an Excel range as a picture before pasting into PowerPoint. Word allows the option of pasting a range as Formatted Text, which means as a Word Table, so I don't have to paste a picture of an Excel range into Word.

Note: Copying a chart as a picture eliminates the ability to reformat the chart using Excel's chart formatting features. To copy an Excel chart and retain this formatting ability, first copy the chart and just the specific data it is based on into an empty workbook. Then copy the chart the "regular" way and paste it as an Excel chart into the target document.

To copy a selected Excel chart or range object as a picture, hold down the Shift key while opening the Edit menu. The Copy command becomes Copy Picture..., which brings up the following dialog box, with my preferred options selected:

When copying an embedded chart:

When copying a worksheet range or chart sheet:

Choosing the Picture Format option allows you to ungroup and adjust the elements of the imported chart within the target application. Using the As shown on screen options places no limitation on the size of the image you can copy. If you choose the As shown when printed option, however, and if the chart or range you are copying is larger than the margins of a printed page, you will get this warning:

When copying a chart sheet, notice there is no option to select its size. You lose control over the size and over other formatting if you rely on chart sheets. I always use charts embedded in worksheets, to control sizes and formatting of my copied charts. The gridlines of the worksheet make it very easy to align charts and size them consistently: hold down the Alt key while moving or sizing a chart, and the chart's edges will cling to the worksheets gridlines.

Summary:

When pasting Excel charts into other applications:

  • Use charts embedded in worksheets for greater control over the size and formatting of the charts.
  • Copy the chart as a picture, to minimize the data transferred with the chart.
  • Alternatively, copy the chart and only its specific data into an empty workbook, and copy-paste this as a native chart.
  • Use the 'As shown on screen' and 'Picture' options for greater flexibility.

When pasting Excel worksheet ranges into other applications:

  • Copy the range normally, then paste as formatted text into Word.
  • Copy the range as a picture, to paste into PowerPoint.
  • Use the 'As shown on screen' and 'Picture' options for greater flexibility.

Using VBA for Excel-Office Interactions


Copying as a Picture Within Excel

It is usually easiest to work on the active chart or range in Excel. The user would select something, then run a macro from a command bar button or shortcut key. To copy the active embedded chart as a picture, according to the methodology described above, use this syntax:

ActiveChart.CopyPicture _
    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

To copy the active chart sheet as a picture, use this syntax:

ActiveChart.CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

To copy the selected worksheet range as a picture as above, use this syntax:

Selection.CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

Sometimes you might prefer to work on a generally defined range or chart, not the selected object. To copy a general embedded chart as a picture, use this syntax:

Worksheets("Sheet Name").ChartObjects(1).Chart.CopyPicture _
    Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

To copy a general chart sheet as a picture, use this syntax:

Charts("Chart Sheet Name").CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

To copy a general worksheet range as a picture, use this syntax:

Worksheets("Sheet Name").Range("A1:D4").CopyPicture _
    Appearance:=xlScreen, Format:=xlPicture

Activating Other Applications with Excel VBA

The first important step to enable Excel to interact with other applications is to set a reference to the other application's object library. In the Visual Basic Editor (VBE), select References... from the Tools menu and the following dialog box appears:

Scroll down the list of available references until you encounter the required object library. Notice in this dialog box, I have already checked references to Word and PowerPoint. The version number 8.0 refers to Office 97 applications; 9.0 through 11.0 refer to Office 2000, Office XP (2002), and Office 2003.

At the top of your procedure you need to declare some object variables specific to the application being automated. To automate Microsoft Other, you would declare the following variables:

Dim otherApp As Other.Application
Dim otherDoc As Other.DocType
Dim otherSpecificObjects As Other.SpecificObjects

Then to open a new instance of Other:

Set otherApp = CreateObject("Other.Application")

or to use an existing instance of Other:

Set otherApp = GetObject(, "Other.Application")
Example 1: Creating New PowerPoint Objects

To open a new PowerPoint instance, create a new file, do some stuff, save and close the file, and quit PowerPoint, you would code something like this:

Sub ExcelToNewPowerPoint()
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide

    ' Create instance of PowerPoint
    Set PPApp = CreateObject("Powerpoint.Application")

    ' For automation to work, PowerPoint must be visible
    ' (alternatively, other extraordinary measures must be taken)
    PPApp.Visible = True

    ' Create a presentation
    Set PPPres = PPApp.Presentations.Add

    ' Some PowerPoint actions work best in normal slide view
    PPApp.ActiveWindow.ViewType = ppViewSlide

    ' Add first slide to presentation
    Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)

    ''---------------------
    '' Do Some Stuff Here
    ''---------------------

    ' Save and close presentation
    With PPPres
        .SaveAs "C:\My Documents\MyPreso.ppt"
        .Close
    End With

    ' Quit PowerPoint
    PPApp.Quit

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing

End Sub
Example 2: Using Active PowerPoint Objects

To use the active slide in the active PowerPoint presentation, your procedure would look something like this:

Sub ExcelToExistingPowerPoint()
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide

    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")

    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation

    ' Some PowerPoint actions work best in normal slide view
    PPApp.ActiveWindow.ViewType = ppViewSlide

    ' Reference active slide
    Set PPSlide = PPPres.Slides _
        (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex) 

    ''---------------------
    '' Do Some Stuff Here
    ''---------------------

    ' Save the presentation
    PPPres.Save

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing

End Sub
Example 3: Using Active PowerPoint Objects if They Exist

The following procedure checks for the active PowerPoint objects. If it finds an existing object, it uses that object; otherwise it creates new objects as needed. This adds a dimension of error-proofing to your code (see Error Free VBA).

Sub ExcelToExistingPowerPoint()
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide

    ' Reference instance of PowerPoint
    On Error Resume Next
    ' Check whether PowerPoint is running
    Set PPApp = GetObject(, "PowerPoint.Application")
    If PPApp Is Nothing Then
        ' PowerPoint is not running, create new instance
        Set PPApp = CreateObject("PowerPoint.Application")
        ' For automation to work, PowerPoint must be visible
        PPApp.Visible = True
    End If
    On Error GoTo 0

    ' Reference presentation and slide
    On Error Resume Next
    If PPApp.Windows.Count > 0 Then
        ' There is at least one presentation
        ' Use existing presentation
        Set PPPres = PPApp.ActivePresentation
        ' Use active slide
        Set PPSlide = PPPres.Slides _
            (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex) 
    Else
        ' There are no presentations
        ' Create new presentation
        Set PPPres = PPApp.Presentations.Add
        ' Add first slide
        Set PPSlide = PPPres.Slides.Add(1, ppLayoutBlank)
    End If
    On Error GoTo 0

    ' Some PowerPoint actions work best in normal slide view
    PPApp.ActiveWindow.ViewType = ppViewSlide

    ''---------------------
    '' Do Some Stuff Here
    ''---------------------

    ' Save the presentation
    PPPres.Save

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing

End Sub
Early vs. Late Binding

Most of the examples on this page use Early Binding to associate the application running the VBA procedure with the other application. For an elementary discussion of Early and Late Binding, refer to Early vs. Late Binding and the links it provides to more comprehensive descriptions.


Using VBA to Paste from Excel to PowerPoint or Word

Based on documentation from Microsoft's Knowledge Base and the online help, plus many bits of code I've snagged from various newsgroups, I have put together some demo procedures to insert Excel objects into PowerPoint slides or Word documents. Unless noted otherwise, these macros are designed to be run from Excel's VB Environment.


Paste the Active Excel Chart into the Active PowerPoint Slide (Early Binding)

This procedure copies the active embedded chart as a picture from an Excel worksheet, then pastes it into the active slide of a PowerPoint presentation, using early binding to the PowerPoint object model.

Sub ChartToPresentation()
' Uses Early Binding to the PowerPoint Object Model
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a chart is selected
If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
        "No Chart Selected"
Else
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides _
        (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture

    ' Paste chart
    PPSlide.Shapes.Paste.Select
    
    ' Align pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub
Positioning the Chart on the Slide

This procedure uses the following block of code to center the picture of the chart within the slide.

    ' Align pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

You can replace it with the following block of code to locate the chart at a specific position on the slide. In this case, the left edge of the chart is positioned 100 points from the left of the slide and the top edge of the chart is positioned 50 points from the top of the slide.

    ' Position pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Left = 100
    PPApp.ActiveWindow.Selection.ShapeRange.Top = 50

List of VBA Examples


Paste the Active Excel Chart into the Active PowerPoint Slide (Late Binding)

This procedure copies the active embedded chart as a picture from an Excel worksheet, then pastes it into the active slide of a PowerPoint presentation. It is identical to the procedure above, except that it uses (see Early vs. Late Binding) late binding; changes are highlighted in green. In late binding, specific references to variable types in the PowerPoint object library must be replaced by generic Object variables, and PowerPoint constants must be replaced by their numeric equivalents. I like to keep the PowerPoint equivalents in a comment to help document the code.

Sub ChartToPresentation()
' Uses Late Binding to the PowerPoint Object Model
' No reference required to PowerPoint Object Library

Dim PPApp As Object ' As PowerPoint.Application
Dim PPPres As Object ' As PowerPoint.Presentation
Dim PPSlide As Object ' As PowerPoint.Slide

' Make sure a chart is selected
If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
        "No Chart Selected"
Else
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = 1 ' 1 = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides _
        (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture

    ' Paste chart
    PPSlide.Shapes.Paste.Select
    
    ' Align pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub

List of VBA Examples


Paste a Selected Excel Worksheet Range into the Active PowerPoint Slide

This procedure copies the selected worksheet range as a picture from an Excel worksheet, then pastes it into the active slide of a PowerPoint presentation.

Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy the range as a piicture
    Selection.CopyPicture Appearance:=xlScreen, _
        Format:=xlPicture

    ' Paste the range
    PPSlide.Shapes.Paste.Select
    
    ' Align the pasted range
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub

List of VBA Examples


Paste a Selected Excel Worksheet Range into the Active PowerPoint Slide (PowerPoint VBA)

This procedure copies the selected worksheet range as a picture from an Excel worksheet, then pastes it into the active slide of a PowerPoint presentation. This macro runs from PowerPoint's VB Environment.

Sub RangeToPresentation()
' Set a VBE reference to Microsoft Excel Object Library
    
    Dim XLApp As Excel.Application
    Dim PPSlide As Slide
    
    ' Reference existing instance of Excel
    Set XLApp = GetObject(, "Excel.Application")

    ' Make sure a range is selected
    If Not TypeName(XLApp.Selection) = "Range" Then
        MsgBox "Please select a worksheet range and try again.", _
            vbExclamation, "No Range Selected"
    Else
        ' Can only paste into slide view
        Application.ActiveWindow.ViewType = ppViewSlide
        ' Reference active slide
        Set PPSlide = ActivePresentation.Slides _
            (Application.ActiveWindow.Selection.SlideRange.SlideIndex)
        
        ' Copy the range as a piicture
        XLApp.Selection.CopyPicture Appearance:=xlScreen, _
            Format:=xlPicture
    
        ' Paste the range
        PPSlide.Shapes.Paste.Select
        
        ' Align the pasted range
        Application.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        Application.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    
        ' Clean up
        Set PPSlide = Nothing
    End If
    
    Set XLApp = Nothing
End Sub

List of VBA Examples


Paste the Active Excel Chart at the Cursor in the Active Word Document

This procedure copies the active embedded chart as a picture from an Excel worksheet, then pastes it at the cursor location of the active Word document.

Sub ChartToDocument()
' Set a VBE reference to Microsoft Word Object Library

Dim WDApp As Word.Application
Dim WDDoc As Word.Document

' Make sure a chart is selected
If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
        "No Chart Selected"
Else
    ' Reference existing instance of Word
    Set WDApp = GetObject(, "Word.Application")
    ' Reference active document
    Set WDDoc = WDApp.ActiveDocument
    
    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture

    ' Paste chart at cursor position
    WDApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
        Placement:=wdInLine, DisplayAsIcon:=False

    ' Clean up
    Set WDDoc = Nothing
    Set WDApp = Nothing
End If

End Sub

List of VBA Examples


Paste the Selected Excel Worksheet Range at the Cursor in the Active Word Document

This procedure copies the selected worksheet range as a picture from an Excel worksheet, then pastes it at the cursor location in the Active Word Document.

Sub RangeToDocument()
' Set a VBE reference to Microsoft Word Object Library

Dim WDApp As Word.Application
Dim WDDoc As Word.Document

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    ' Reference existing instance of Word
    Set WDApp = GetObject(, "Word.Application")
    ' Reference active document
    Set WDDoc = WDApp.ActiveDocument
    ' Reference active slide
    
    ' Copy the range
    Selection.Copy

    ' Paste the range
    WDApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, _
        Placement:= wdInLine, DisplayAsIcon:=False

    ' Clean up
    Set WDDoc = Nothing
    Set WDApp = Nothing
End If

End Sub

List of VBA Examples


Paste Each Embedded Chart in the Active Worksheet into a New Slide in the Active Presentation

This procedure copies each embedded chart in the active worksheet as a picture from an Excel worksheet, then pastes it into a new slide at the end of a PowerPoint presentation.

Sub ChartsToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
    ' copy chart as a picture
    ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
        Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

    ' Add a new slide and paste in the chart
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    With PPSlide
        ' paste and select the chart picture
        .Shapes.Paste.Select
        ' align the chart
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

List of VBA Examples


Paste Each Embedded Chart in the Active Worksheet into a New Slide in the Active Presentation, using the Chart Title as the Slide Title

This procedure copies each embedded chart in the active worksheet as a picture from an Excel worksheet, then pastes it into a new slide at the end of a PowerPoint presentation. The title of the embedded chart is used as the title of the new slide.

Sub ChartsAndTitlesToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle As String

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
    With ActiveSheet.ChartObjects(iCht).Chart
        
        ' get chart title
        If .HasTitle Then
            sTitle = .ChartTitle.Text
        Else
            sTitle = ""
        End If
        
        ' remove title (or it will be redundant)
        .HasTitle = False
        
        ' copy chart as a picture
        .CopyPicture _
            Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
        
        ' restore title
        If Len(sTitle) > 0 Then
            .HasTitle = True
            .ChartTitle.Text = sTitle
        End If
    End With
    
    ' Add a new slide and paste in the chart
    SlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
    PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    With PPSlide
        ' paste and select the chart picture
        .Shapes.Paste.Select
        ' align the chart
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
        .Shapes.Placeholders(1).TextFrame.TextRange.Text = sTitle
    End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

List of VBA Examples


Paste Selected Embedded Charts onto the Active PowerPoint Slide

This procedure copies each selected embedded chart in the active worksheet as a picture, then pastes it onto the active slide in the active PowerPoint presentation.

Sub CopyChartsIntoPowerPoint()
''' COPY SELECTED EXCEL CHARTS INTO POWERPOINT
' Set a VBE reference to Microsoft PowerPoint Object Library

Dim pptApp As PowerPoint.Application
Dim iShapeIx As Integer, iShapeCt As Integer
Dim myShape As Shape, myChart As ChartObject
Dim bCopied As Boolean

Set pptApp = GetObject(, "PowerPoint.Application")

If ActiveChart Is Nothing Then
    ''' SELECTION IS NOT A SINGLE CHART
    On Error Resume Next
    iShapeCt = Selection.ShapeRange.count
    If Err Then
        MsgBox "Select charts and try again", vbCritical, "Nothing Selected"
        Exit Sub
    End If
    On Error GoTo 0
    For Each myShape In Selection.ShapeRange
        ''' IS SHAPE A CHART?
        On Error Resume Next
        Set myChart = ActiveSheet.ChartObjects(myShape.name)
        If Not Err Then
            bCopied = CopyChartToPowerPoint(pptApp, myChart)
        End If
        On Error GoTo 0
    Next
Else
    ''' CHART ELEMENT OR SINGLE CHART IS SELECTED
    Set myChart = ActiveChart.Parent
    bCopied = CopyChartToPowerPoint(pptApp, myChart)
End If

Dim myPptShape As PowerPoint.Shape
Dim myScale As Single
Dim iShapesCt As Integer

''' BAIL OUT IF NO PICTURES ON SLIDE
On Error Resume Next
iShapesCt = pptApp.ActiveWindow.Selection.SlideRange.Shapes.count
If Err Then
    MsgBox "There are no shapes on the active slide", vbCritical, "No Shapes"
    Exit Sub
End If
On Error GoTo 0

''' ASK USER FOR SCALING FACTOR
myScale = InputBox(Prompt:="Enter a scaling factor for the shapes (percent)", _
    Title:="Enter Scaling Percentage") / 100

''' LOOP THROUGH SHAPES AND RESCALE "PICTURES"
For Each myPptShape In pptApp.ActiveWindow.Selection.SlideRange.Shapes
    If myPptShape.name Like "Picture*" Then
        With myPptShape
            .ScaleWidth myScale, msoTrue, msoScaleFromMiddle
            .ScaleHeight myScale, msoTrue, msoScaleFromMiddle
        End With
    End If
Next

Set myChart = Nothing
Set myShape = Nothing
Set myPptShape = Nothing
Set pptApp = Nothing
End Sub

Function CopyChartToPowerPoint(oPPtApp As PowerPoint.Application, _
    oChart As ChartObject)
CopyChartToPowerPoint = False

oChart.Chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen
oPPtApp.ActiveWindow.View.Paste

CopyChartToPowerPoint = True
End Function

List of VBA Examples

 

Peltier Tech Chart Utility


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile