Multiple Excel Charts to PowerPoint 2

[SteveG]

I am following on from an older post
http://groups.google.com/group/microsoft.public.excel.charting/msg/a96af87f70b6657f?

I have used Jon Peltier's page http://peltiertech.com/Excel/XL_PPT.html#chartppt
to try and create a macro that will open a new PPT, copy all th
echartsheets and paste as bitmap (to stop people falsifying charts!)
one to a ppt slide.

My problems are:

1) The macro only seems to work if PPT is already running (despite
using Jon's code for creating a New PPT object)

2) The bitmap solution is ok when on screen but I get people asking if
their eyes are playing tricks when we look at the printed copy. Yes
it is a bit fuzzy! What controls the resolution of a bitmap? Is it
the screen resolution?

Is there any way to prevent ungrouping of a picture?

3) Can I define the PPT template (.pot) that should be used?

4) What decides the dimensions (points, inches, cm) used for
positioning? - Are they set by the Windows regional settings?

The code is below (you can see where I have added sections - extra
indent)

I am so close to my goal but am stuck with these last few points!

Can anyone help?

Steve




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


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

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


' 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

For iCht = 1 To ActiveWorkbook.Charts.Count
' Copy chartsheet as a picture
ActiveWorkbook.Charts(iCht).CopyPicture _
Appearance:=xlScreen, Size:=xlScreen,
Format:=xlBitmap

'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
' position the chart
With PPApp.ActiveWindow.Selection.ShapeRange
.Top = 94 ' points
.Left = 58 ' points
.Width = 8.2 * 72
.Height = 5.6 * 72
End With
End With


Next


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


End Sub

 

[Jon Peltier]

You neglected to mention which version of Office you're using.

Bitmaps are only good if they are not resized and not antialiased. Their
resolution is based on the screen: one screen pixel = one bitmap pixel.
Until 2007, the Picture format was better, but now it has been degraded, but
it at least doesn't change in quality as it is resized.

I don't know why PowerPoint won't start using CreateObject. What feedback
does your machine provide? You should be able to define the PowerPoint
template to use. If it's anything like Excel, something like this should
work:

Set PPPres = PPApp.Presentations.Add("TemplatePathAndFile.pot")

I'll leave it up to you to look it up in the PowerPoint object model (use
the Object Browser in the VB Editor).

When you get your image into PowerPoint, the dimensions you use in VBA for
positioning are points.

- Jon

 

[Steve31530]

I am using Excel 2000 with ppt 2003 under XP.

This is a strange mix but we should align on Office '07 later this
year.

Thanks for the tips.

Steve

 

[Steve31530]

Oh ... and when I run the macro there is no sign that there is a
problem... except that PPT never opens and there is no resulting ppt
file.

Steve

 

[Andy Pope]

Hi,

Some of the examples code do not include the line to make the powerpoint
application visible. Automation will not work for PP unless the
applicaition is visible.

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

Cheers
Andy

 

[Jon Peltier]

Oh, good point. Slipped my mind.

I've updated my web page to reflect this.
http://peltiertech.com/Excel/XL_PPT.html

- Jon

 

[Steve31530]

.... and now it works!

Thanks!

Steve

 

[Steve31530]

Bitmaps are only good if they are not resized and not antialiased. Their resolution is based on the screen: one screen pixel = one bitmap pixel.

If I understand you correctly I should 'pre-set' the size of the chart
so that BitMap doesn't have to be resized in PPT. To me this means
that I should NOT select View>Sized to window but rather work out what
zoom % is required to end-up with the correct sized image in PPT and
select this by using View>Zoom>Custom X%? Do I have to do anything
with the size or appearance in teh VBA?

How do I avoid anti-aliasing? What action would cause this as I paste
images in PPT? (Clearly I do not know what I am doing!)

Until 2007, the Picture format was better, but now it has been degraded, but it at least doesn't change in quality as it is resized.

When you say the picture format was better do you mean Excel 2003 was
better than Excel2007 or are you saying picture was better than
bitmap prior to Excel 2007? I think there was something in the
reader's comments on your blog but I can find it now.

I am in a situation where people have taken my PPT slides and modified
the data to suit their own story (there has to be a Dilbert in this!)
As a result I have been using bitmaps pasted in PPT. If you are
saying that when we get Excel2007 I will need to change techniques
then I had better start now!

Thanks for your light in the darkness!

Steve

 

[Jon Peltier]

If I understand you correctly I should 'pre-set' the size of the chart
so that BitMap doesn't have to be resized in PPT. To me this means
that I should NOT select View>Sized to window but rather work out what
zoom % is required to end-up with the correct sized image in PPT and
select this by using View>Zoom>Custom X%? Do I have to do anything
with the size or appearance in teh VBA?

Instead of using standalone chart sheets, embed your chart on a worksheet.
Keep window zoom at 100, and resize the chart as you would any shape. This
makes sizing much easier.

PowerPoint (2007 and 2003) does some internal processing of the image so
that resizing a bitmap isn't as traumatic as it once was.

How do I avoid anti-aliasing? What action would cause this as I paste
images in PPT? (Clearly I do not know what I am doing!)

I think 2007 automatically carries out antialiasing. I don't know how to
prevent it, as I haven't worked that much in 2007.

When you say the picture format was better do you mean Excel 2003 was
better than Excel2007 or are you saying picture was better than
bitmap prior to Excel 2007? I think there was something in the
reader's comments on your blog but I can find it now.

Yes to both parts. It might be that BMP is prettyy much the same.

I am in a situation where people have taken my PPT slides and modified
the data to suit their own story (there has to be a Dilbert in this!)
As a result I have been using bitmaps pasted in PPT. If you are
saying that when we get Excel2007 I will need to change techniques
then I had better start now!

If you're pasting bitmaps, and the results are acceptable, keep pasting
bitmaps. I understand the issue with people fudging the data. It describes a
former situation I was involved in where even so-called certified QA data
was untrustworthy.

- Jon