software

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 quot;responderquot; 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(, quot;Powerpoint.Applicationquot;)
' 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 SubMany thanks in advance!

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
PeltierTech.com/
_______quot;mustang25quot; gt; wrote in message
...
gt; The message below is in response to a solution for my question of whether
gt; it
gt; is possible to build a macro that will export multiple Excel charts to a
gt; PowerPoint presentation with one chart to a slide. The quot;responderquot; gave
gt; me
gt; an ingenious solution that works perfectly. My next question is if those
gt; charts can be automatically resized. Keep reading for details.
gt;
gt; Jon,
gt;
gt; This is working brilliantly. I'd like to throw one more challenge at you
gt; if
gt; you don't mind. Is there a way to output the charts to PowerPoint with
gt; the
gt; following parameters:
gt;
gt; Height 5.66 inches
gt; Width 9.66 inches
gt; Horizontal Position 0 inches from top left corner
gt; Vertical Position 1 inch from top left corner
gt;
gt; Here is the current code I'm using for the macro (you'll notice that I
gt; removed the line/command that copies the chart as a picture. This is
gt; intentional.):
gt;
gt; Sub ChartsToPresentation()
gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt;
gt; Dim PPApp As PowerPoint.Application
gt; Dim PPPres As PowerPoint.Presentation
gt; Dim PPSlide As PowerPoint.Slide
gt; Dim PresentationFileName As Variant
gt; Dim SlideCount As Long
gt; Dim iCht As Integer
gt;
gt; ' Reference existing instance of PowerPoint
gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt; ' Reference active presentation
gt; Set PPPres = PPApp.ActivePresentation
gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt;
gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt; ' copy chart as a picture
gt; ActiveSheet.ChartObjects(iCht).Copy
gt;
gt; ' Add a new slide and paste in the chart
gt; SlideCount = PPPres.Slides.Count
gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt; With PPSlide
gt; ' paste and select the chart picture
gt; .Shapes.Paste.Select
gt; ' align the chart
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; End With
gt;
gt; Next
gt;
gt; ' Clean up
gt; Set PPSlide = Nothing
gt; Set PPPres = Nothing
gt; Set PPApp = Nothing
gt;
gt; End Sub
gt;
gt;
gt; Many thanks in advance!
Jon,

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

quot;Jon Peltierquot; wrote:

gt; A few comments:
gt;
gt; 1. Make the chart in Excel the right size for its final location in
gt; PowerPoint. Resizing imported charts can be an aggravating exercise,
gt; especially if the aspect ratio of the chart has to be adjusted. If you need
gt; charts another size for optimized viewing in Excel too, then make two sets
gt; of charts.
gt;
gt; 2. It's more reliable to use chart sheets for sizing, and adjust size of the
gt; chart by adjusting the page margins.
gt;
gt; 3. The most reliable technique of all, and also the most complicated
gt; (naturally), is to create a new workbook, with the chart on a
gt; properly-margined chart sheet, and the data for the chart on a worksheet.
gt; Make the chart sheet the active sheet, save and close the new workbook, and
gt; insert it in PowerPoint as an inserted object, created from a file. I have a
gt; sample somewhere, but not at my fingertips.
gt;
gt; - Jon
gt; -------
gt; Jon Peltier, Microsoft Excel MVP
gt; Peltier Technical Services
gt; Tutorials and Custom Solutions
gt; PeltierTech.com/
gt; _______
gt;
gt;
gt; quot;mustang25quot; gt; wrote in message
gt; ...
gt; gt; The message below is in response to a solution for my question of whether
gt; gt; it
gt; gt; is possible to build a macro that will export multiple Excel charts to a
gt; gt; PowerPoint presentation with one chart to a slide. The quot;responderquot; gave
gt; gt; me
gt; gt; an ingenious solution that works perfectly. My next question is if those
gt; gt; charts can be automatically resized. Keep reading for details.
gt; gt;
gt; gt; Jon,
gt; gt;
gt; gt; This is working brilliantly. I'd like to throw one more challenge at you
gt; gt; if
gt; gt; you don't mind. Is there a way to output the charts to PowerPoint with
gt; gt; the
gt; gt; following parameters:
gt; gt;
gt; gt; Height 5.66 inches
gt; gt; Width 9.66 inches
gt; gt; Horizontal Position 0 inches from top left corner
gt; gt; Vertical Position 1 inch from top left corner
gt; gt;
gt; gt; Here is the current code I'm using for the macro (you'll notice that I
gt; gt; removed the line/command that copies the chart as a picture. This is
gt; gt; intentional.):
gt; gt;
gt; gt; Sub ChartsToPresentation()
gt; gt; ' Set a VBE reference to Microsoft PowerPoint Object Library
gt; gt;
gt; gt; Dim PPApp As PowerPoint.Application
gt; gt; Dim PPPres As PowerPoint.Presentation
gt; gt; Dim PPSlide As PowerPoint.Slide
gt; gt; Dim PresentationFileName As Variant
gt; gt; Dim SlideCount As Long
gt; gt; Dim iCht As Integer
gt; gt;
gt; gt; ' Reference existing instance of PowerPoint
gt; gt; Set PPApp = GetObject(, quot;Powerpoint.Applicationquot;)
gt; gt; ' Reference active presentation
gt; gt; Set PPPres = PPApp.ActivePresentation
gt; gt; PPApp.ActiveWindow.ViewType = ppViewSlide
gt; gt;
gt; gt; For iCht = 1 To ActiveSheet.ChartObjects.Count
gt; gt; ' copy chart as a picture
gt; gt; ActiveSheet.ChartObjects(iCht).Copy
gt; gt;
gt; gt; ' Add a new slide and paste in the chart
gt; gt; SlideCount = PPPres.Slides.Count
gt; gt; Set PPSlide = PPPres.Slides.Add(SlideCount 1, ppLayoutBlank)
gt; gt; PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
gt; gt; With PPSlide
gt; gt; ' paste and select the chart picture
gt; gt; .Shapes.Paste.Select
gt; gt; ' align the chart
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
gt; gt; PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
gt; gt; End With
gt; gt;
gt; gt; Next
gt; gt;
gt; gt; ' Clean up
gt; gt; Set PPSlide = Nothing
gt; gt; Set PPPres = Nothing
gt; gt; Set PPApp = Nothing
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Many thanks in advance!
gt;
gt;
gt;