XL chart sheets Custom size for PPT

[Brian Reilly, MVP]

Hi folks,
I have an app that uses XL chart sheets rather than charts on a
worksheet. I prefer that, but am not married to that. But kinda like
the approach of using chart sheets from experience over the years.

Here's the issue:
I have say the x and y axis and data value fonts set to 12 points
Verdana. I use VBA code to copy the data sheet and the chart sheet to
a new workbook and copy the color palette from the original workbook.
I then save the file as somepath\filetokill.xls and then switch via
code to PPT and use insert object to insert that chart and datasheet
into PPT make sure it is sized at 100% or original size so any 12
point fonts in PPT will match the 12 point fonts in XL chart. That
works great for full chart sheet sizes.

Now the problem.

I may want smaller custom sized charts say 3" x 3" from XL to come in
at exactly that size in PPT (both apps can handle Points and not
inches (72 points to an inch). I used to do this with custom sized
chart sheets but that is not working at the moment for reasons that
evade me now.

You folks have any directions or pointers to managing custom sized
chart sheets and sizing?

Thanks to all, Happy New Year.

Brian Reilly, PowerPoint MVP

 

[Jon Peltier]

How did you customize the chart sheets' sizes? I've done it successfully by
leaving the page size alone and fiddling with the margins. I haven't done it
very much.

- Jon

 

[Brian Reilly, MVP]

Hi Jon,
Yes I was fiddling with the margins and getting to sort of the right
size in XL. The problem occurs when I use copy and paste that it
ignores the "custom size" page when pasted to PPT. BUT, all is better
now. If I use Insert Object From File in PPT I get the right size
chart and font sizes match up from XL to PPT font sizes.

That one is a keeper. So I don't forget it again,. I'll write up a
short article for the PPTFAQ.com site which will get Googleized so I
can find my own stuff again when I forget what I did. You know I won't
be able to find it again on my hard drives. Might as well let Steve
Rindsberg and Google be my librarians, eh?

Actually I can do this with the PageSetup settings. Then I move the
chart sheet and data sheet to another workbook and copy the color
palette (you just knew I was using a custom color palette didn't you?
(vbg)). Then I save the file as something like FileToKill.xls.

Then I use Insert Object from File in PPT and use scaleheight and
scale width to 100% just to be sure. And then position the top and
left. Then I kill the FileToKill.xls file since I don't need it
anymore.

So now all that is left is to figure out the full page size of a chart
sheet (width and height) so I can just draw a rectangle in PPT and get
those width and height values and do the arithmetic to get the margins
in Xl so I can write them to a table in XL for future re-use as
variables.

Thanks,
Brian Reilly, PowerPoint MVP

 

[Jon Peltier]

Brian -

I assumed you were inserting from file. Copy/Paste of a workbook object
(i.e., a chart) is iffy.

Use 8.5*11 as your page size, and subtract the margins.

I believe Stephanie Krieger has an example somewhere on her blog/web site
(http://www.arouet.net/) that describes this process with a Word doc as the
target. I found this link on her site:

http://www.arouet.net/archives/2004/11/sizing_excel_ch.php

- Jon

 

[Brian Reilly, MVP]

Jon,
Thanks for the link. I was not surprised to see your name in the blog,
but was surprised to see mine. I notice you still like that copy as
picture routine (vbg). I use that sometimes, nothing wrong with it but
still like having the data. Why don't you add a short example routine
to your web site describing the copy to another workbook and
copycolors technique that I use all the time. I certainly didn't
invent it, learned it from Bovey and I bet he didn't invent it either,
but he was the first one to point it out to me years ago and I'm still
amazed at how many people don't know it. Let us know when you do and
we'll add a link to it on the pptfaq.com site.

Brian Reilly, PowerPoint MVP

 

[Jon Peltier]

In my copious free time....

- Jon

 

[Brian Reilly, MVP]

Jon, Well, if you don't get to it in your copious free time. we'll
just try to get to it in our "copy its" free time. All will be
appropriately credited. Except, maybe Bovey, he won't notice. He's
probably out in the back yard feeding his hounds.

Regards and Happy New Year.

Brian Reilly, PowerPoint MVP