|
|
|
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
|
|
