Automatically resize excel charts when exporting them to PowerPoin

[mustang25]

The message below is in response to a solution for my question of whether it
is possible to build a macro that will export multiple Excel charts to a
PowerPoint presentation with one chart to a slide. The "responder" gave me
an ingenious solution that works perfectly. My next question is if those
charts can be automatically resized. Keep reading for details.

Jon,

This is working brilliantly. I'd like to throw one more challenge at you if
you don't mind. Is there a way to output the charts to PowerPoint with the
following parameters:

Height 5.66 inches
Width 9.66 inches
Horizontal Position 0 inches from top left corner
Vertical Position 1 inch from top left corner

Here is the current code I'm using for the macro (you'll notice that I
removed the line/command that copies the chart as a picture. This is
intentional.):

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).Copy

' 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


Many thanks in advance!

 

[Jon Peltier]

A few comments:

1. Make the chart in Excel the right size for its final location in
PowerPoint. Resizing imported charts can be an aggravating exercise,
especially if the aspect ratio of the chart has to be adjusted. If you need
charts another size for optimized viewing in Excel too, then make two sets
of charts.

2. It's more reliable to use chart sheets for sizing, and adjust size of the
chart by adjusting the page margins.

3. The most reliable technique of all, and also the most complicated
(naturally), is to create a new workbook, with the chart on a
properly-margined chart sheet, and the data for the chart on a worksheet.
Make the chart sheet the active sheet, save and close the new workbook, and
insert it in PowerPoint as an inserted object, created from a file. I have a
sample somewhere, but not at my fingertips.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[mustang25]

Jon,

I shall try comment number 3 the next time I do a similar project (soon).
Thank you very much for your invaluable assistance.