Excel CopyPicture and PowerPoint

[Bo]

Hi all,

Have a problem with picture resolution when copying excel charts to
PowerPoint.
The code below (compiled from internet) works but generates bad resolution
when invoked from button in excel. If I run the code manually (step by step)
the first slide comes out great, but with the second slide I get an
automation when resizing the image.

Any suggestions?
Thanks Bo


#### VBA

Sub CTP()

Dim ChartName As Variant
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim CurrentTitle As Variant
Dim SlideCount As Long
Dim ch As Chart

Set PPApp = CreateObject("Powerpoint.Application")
Set PPPres = PPApp.Presentations.Add

PPApp.Visible = msoTrue
CurrentTitle = "Testing"
PresentationFileName = PPApp.ActivePresentation.Path
PresentationFileName = PresentationFileName & CurrentTitle & ".ppt"

For Each ch In ActiveWorkbook.Charts

ch.Activate
ch.CopyPicture xlScreen, xlPicture, xlScreen
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)

With PPSlide
.Shapes.Paste.Select
'resize image
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters,
True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles,
True
PPApp.ActiveWindow.Selection.ShapeRange.Height = 505.88
PPApp.ActiveWindow.Selection.ShapeRange.Width = 720
PPApp.ActiveWindow.Selection.ShapeRange.Left = 0
PPApp.ActiveWindow.Selection.ShapeRange.Top = 17
PPApp.ActiveWindow.Selection.Unselect
End With

SlideCount = SlideCount + 1

Next

With PPPres
' .SaveAs "C:\test.ppt"
.Close
End With
PPApp.Quit

Set PPApp = Nothing
Set PPPres = Nothing

End Sub

####

 

[Bo]

Hi again,

Got rid of the automation error by inserting

"PPPres.Slides.Item(SlideCount + 1).Select" before the " With PPSlide" -row

Now the problem is more clear:
Invoking the macro from a button in excel generates poor resoluton images,
but runing it manually generates clear images. Running Office 2k on W2k
professional.

Thanks //Bo

 

[Jon Peltier]

Bo -

Is the poor resolution due to resizing a bitmap (gif/jpg)? What happens
when you double click the pasted chart? The code you posted should paste
in a metafile (picture), which will try to ungroup itself on the double
click. So make sure the button is actually running the same code.

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

 

[Keith R]

Sorry if this is a silly question, but on the following line:

are you sure these the right parameters to use for [(appearance, format,
size)]? If I were to guess, I'd think it was (appearance, appearance,
appearance) and I'm not sure how XL would interpret that, if it is the case.

Also, is there a difference in quality between copypicture xlscreen vs
xlpicture? I assume xlpicture gives the same things as "copy as printed",
which I would think might provide a higher resolution than xlscreen, but
then, I haven't tested to be sure....

 

[Bo]

I am such an idiot...

Struggling with this has created many versions of code and yes, the button
was connected to wrong ver
ersion. Thanks Jon for putting me on the track!

//Bo

Sorry if this is a silly question, but on the following line:

are you sure these the right parameters to use for [(appearance, format,
size)]? If I were to guess, I'd think it was (appearance, appearance,
appearance) and I'm not sure how XL would interpret that, if it is the case.

Also, is there a difference in quality between copypicture xlscreen vs
xlpicture? I assume xlpicture gives the same things as "copy as printed",
which I would think might provide a higher resolution than xlscreen, but
then, I haven't tested to be sure....

 

[Jon Peltier]

Bo -

Glad you got it fixed. You didn't have to admit this was the problem,
you know.

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

I am such an idiot...

Struggling with this has created many versions of code and yes, the button
was connected to wrong ver
ersion. Thanks Jon for putting me on the track!

//Bo

Sorry if this is a silly question, but on the following line:

ch.CopyPicture xlScreen, xlPicture, xlScreen

are you sure these the right parameters to use for [(appearance, format,
size)]? If I were to guess, I'd think it was (appearance, appearance,
appearance) and I'm not sure how XL would interpret that, if it is the
case.

Also, is there a difference in quality between copypicture xlscreen vs
xlpicture? I assume xlpicture gives the same things as "copy as printed",
which I would think might provide a higher resolution than xlscreen, but
then, I haven't tested to be sure....

 

[Jon Peltier]

Keith -

Those are the correct settings. Appearance as onscreen I guess means all
the colors will show (if it's a black and white printer, presumably the
colors will be turned to gray). Size as on screen is self-evident.

Picture format means you are copying a metafile, that is, a vector
representation which contains all the data to redraw the chart from its
constituent lines, shapes, and labels. This means you will not get pixel
distortion if you resize the chart, and you can ungroup the chart in the
target application.

Printer format means bitmap, jpg, png, or gif, any format that converts
the objects into pixels. If the image will remain the same size and
shape, this is probably the best, and for web pages, it's the only way
to go. If you resize one of these, you will get jagged edges where the
pixels in the image no longer line up with the pixels in the screen. You
should use png or gif for charts; these formats are good for images with
large regions of a constant fill color. The jpg format is better for
photographic images which have continuously varying gradients of color.

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

Sorry if this is a silly question, but on the following line:



are you sure these the right parameters to use for [(appearance, format,
size)]? If I were to guess, I'd think it was (appearance, appearance,
appearance) and I'm not sure how XL would interpret that, if it is the case.

Also, is there a difference in quality between copypicture xlscreen vs
xlpicture? I assume xlpicture gives the same things as "copy as printed",
which I would think might provide a higher resolution than xlscreen, but
then, I haven't tested to be sure....

 

[Keith R]

I learn something new in this group every day

Keith -

Those are the correct settings. Appearance as onscreen I guess means all
the colors will show (if it's a black and white printer, presumably the
colors will be turned to gray). Size as on screen is self-evident.

Picture format means you are copying a metafile, that is, a vector
representation which contains all the data to redraw the chart from its
constituent lines, shapes, and labels. This means you will not get pixel
distortion if you resize the chart, and you can ungroup the chart in the
target application.

Printer format means bitmap, jpg, png, or gif, any format that converts
the objects into pixels. If the image will remain the same size and
shape, this is probably the best, and for web pages, it's the only way
to go. If you resize one of these, you will get jagged edges where the
pixels in the image no longer line up with the pixels in the screen. You
should use png or gif for charts; these formats are good for images with
large regions of a constant fill color. The jpg format is better for
photographic images which have continuously varying gradients of color.

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

Sorry if this is a silly question, but on the following line:

ch.CopyPicture xlScreen, xlPicture, xlScreen

are you sure these the right parameters to use for [(appearance, format,
size)]? If I were to guess, I'd think it was (appearance, appearance,
appearance) and I'm not sure how XL would interpret that, if it is the case.

Also, is there a difference in quality between copypicture xlscreen vs
xlpicture? I assume xlpicture gives the same things as "copy as printed",
which I would think might provide a higher resolution than xlscreen, but
then, I haven't tested to be sure....