Excel Chart in Word - Strange Printing Problem

[Gary McGill]

I have a Word 2000 document with some embedded Excel (2000) charts. The
charts themselves are quite small, and so use a very small font (Gill Sans
Light @ 5pt). When I print the document, one of the charts looks great, but
the others seem to use the wrong font (it looks like Arial).

Even if I copy the "good" chart, the copy suffers from the same problem as
the others. This is not specific to one machine (a colleague has replicated
the problem), nor to one printer (I can get the same result when printing to
PDF).

My colleague did manage to get all the charts to come out correctly after
some tweaks to the size of the charts - but we can't figure out what's
making the difference, or how to do it reliably.

Any ideas as to (a) what's going wrong, and (b) how to avoid it?

Gary

PS. Some more info:

* Each chart was created in Excel, whereupon I selected the chart, copied
it, then pasted it into Word - thus creating an embedded Excel chart &
worksheet within Word. I don't think this is the problem, though, since all
the charts were created the same way, and one works fine!

* Each chart is un-scaled in Word, by which I mean they are all at 100% x
100% of original. (When I adjust the size of an embedded chart, I always
open the chart and do it in Excel). That said, the "tweaking" that my
colleague did to get the charts to work may have involved some small scaling
adjustments in Word.

* I have the "auto-scale" font-size setting turned off for all the charts.

* Although the charts seem to use the wrong font, it does appear at what
looks like the correct size. Also, it's not "rasterized" - when printed to
PDF I can zoom all the way in and the edges of the characters are still
smooth.

* The font I'm using is a Windows PostScript font. (I had the option of
buying a TrueType or OpenType version, but I have a bias in favour of
PostScript fonts when creating PDFs - which is what this document is for).

* In "print preview", all of the charts look the same (i.e. the "good" one
and the "bad" ones are indistinguishable). None of them look like they use
the correct font - in fact they look very odd, because the character spacing
seems all wrong, as if the character positions were based on the correct
font despite it being drawn with another.

 

[Jon Peltier]

Your starred points answer a number of potential questions. The only
other thing I can think of that might make the charts different is how
each was copied and pasted, and were any done differently. Regular copy,
or copy as a picture (and if so, using what settings)? Paste, paste
link, paste as a picture (and if so, which option)?

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

 

[Keith R]

Have you tried installing SP3? I had some chart label issues, and SP3 fixed
/some/ of them for me... especially one with chart labels oriented
vertically with a (true type) font size of less than 8pt . Might help, might
not...

Also, have you played with your advanced printer 'device settings', things
like your font substitution table? Seems like there are some other
postscript options that might also affect font substitution, like 'download
soft fonts' but I don't recall them all off the top of my head.

 

[Gary McGill]

Jon,

I generally just use Copy...Paste - no special options, no Paste Special or
anything like that.

I have just discovered further evidence that it's not the printer, and not
the way the charts are constructed. I think I mentioned in my original post
that one of my colleagues had managed to get the charts to work by
"tweaking" them somehow. After some experimentation, I've discovered that I
can reproduce this...

If I re-size the charts in Excel (by clicking on the chart and selecting
Open, then resizing the Excel document window) then this has no effect on
the problem. However, if I slightly re-size the charts in Word (by selecting
the chart and dragging the handles), then about 50% of the time, this will
"fix" the problem(!).

I don't think it's about achieving a certain size - it seems to be pretty
random. Unfortunately, the chart doesn't stay fixed if you subsequently open
it to edit the data- you have to go through the whole
tweak-print-tweak-print process again.

(Note: I suspect the reason that one of my copied charts worked before is
that I pasted it into a slightly-too-small table, causing it to get
automatically re-sized by Word?)

So, I was able to fix all the charts in my document by an iterative process
of re-sizing each chart until it was "fixed" (although obviously all the
charts were slightly the wrong size as a result of my tweaking). However,
I'll actually be creating hundreds of these documents automatically through
VBA so it's really not much help to me to have a manual work-around.

I'm really struggling here (there's a lot riding on my ability to find a
solution) - any further help would be greatly appreciated. I can provide a
sample document, if that helps.

Thanks,
Gary


...

 

[Gary McGill]

Keith,

I have SP3 already. Ah - if only it were that simple!

I have my doubts that the printer device settings are the problem, since (a)
one chart works!, and (b) the effect is the same whether I print to my
laserjet or to PDF.

However, I did check the options I'm using, and for the Adobe Generic
PostScript Printer that I use for created PostScript files for conversion
into PDF, I have:
- Graphic/TrueType Font: Download as Softfont
- Document Options/Postscript Options/TrueType Dont Download Option:
Outline

Note however, that the fonts I'm using are NOT TrueType fonts - they're
PostScript fonts.

I also checked the font substitution table in the PostScript Printer
settings, and there were very few substitutions listed (Courier New, and
some symbol fonts).

Thanks for trying, though!

PS. Just discovered that if I slightly resize my charts in Word (by dragging
the handles), the problem goes away (but not reliably)! See my response to
Jon for more details.

 

[Jon Peltier]

Gary -

I've read many posts from people who have had their charts change sizes
on them, but I've rarely had this problem myself. Probably because every
chart I make, I resize and reformat somehow (manually or with VBA). Once
that's been done, Excel must somehow mark the chart as having been
fixed, so it is no longer subject to autosizing. This autosizing must
get worse when Word autosizes and object created--and autosized--by Excel.

Could you put a few lines into the code that change the chart object
size and the plot area size by a point or two? Try this to see if you
get around the issue.

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

 

[Gary McGill]

Jon,

people who have had their charts change sizes on them

I know what you mean - I've had the random-chart-resizing problem happen to
me many times (I was probably one of those posters!). However, in this
instance the chart isn't resizing (I learned how to cure that one!). What's
more, the fonts aren't resizing either - it seems to be using the correct
size of font, but Arial instead of my chosen font. (Sorry if that sounds
pedantic - I just wanted to re-state this since I've been cutting out the
post history in my replies).

put a few lines into the code...

I'll try it (I'll try anything!), but when I was changing the size manually
it didn't always work the first time - I would guess at a 33% success rate -
and since my VBA code will have no way of knowing when it's "fixed", it
won't know when to stop trying. (I also worry that I can un-fix a "good"
chart in exactly the same way - although with the testing I've done it's
never happened).

I've posted an example Word document and corresponding PDF output at
http://www.electrum.co.uk/gary/chart so that you can see what I'm talking
about. Realizing that very few people will have the font I'm using, I tried
to change the font to one of those that come with Word. Guess what: I
couldn't reproduce the problem! Isn't that always the way? The problem
always happens except when you want it to!

Thanks again,
Gary

---

 

[Jon Peltier]

Jon,




I know what you mean - I've had the random-chart-resizing problem happen to
me many times (I was probably one of those posters!). However, in this
instance the chart isn't resizing (I learned how to cure that one!). What's
more, the fonts aren't resizing either - it seems to be using the correct
size of font, but Arial instead of my chosen font. (Sorry if that sounds
pedantic - I just wanted to re-state this since I've been cutting out the
post history in my replies).

Oh yeah, fonts. I meant to chime in on that facet. I remember that it
used to be an issue that if a font became too small to render clearly,
Excel would switch to something like Ariel. But I also remember that was
only an issue with the on-screen fonts.

I'll try it (I'll try anything!), but when I was changing the size manually
it didn't always work the first time - I would guess at a 33% success rate -
and since my VBA code will have no way of knowing when it's "fixed", it
won't know when to stop trying. (I also worry that I can un-fix a "good"
chart in exactly the same way - although with the testing I've done it's
never happened).

I've never unfixed a good chart. In cases like this I pick an arbitrary
loop counter that seems to work. 2 or 4 or, in the case of a macro that
colored the wedges in a pie chart with 50+ slices, 12 or 15. But VBA is
pretty patient, and usually does about what I ask it to.

I've posted an example Word document and corresponding PDF output at
http://www.electrum.co.uk/gary/chart so that you can see what I'm talking
about. Realizing that very few people will have the font I'm using, I tried
to change the font to one of those that come with Word. Guess what: I
couldn't reproduce the problem! Isn't that always the way? The problem
always happens except when you want it to!

Hmmm, sounds like a bad joke.
"Doc, it hurts when I use this font."
"So don't use this font."

Thanks again,
Gary

Glad to (try to) help.

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

 

[Gary McGill]

Jon,

Hmmm, sounds like a bad joke.
"Doc, it hurts when I use this font."
"So don't use this font."

I wish! Unfortunately it's a corporate standard so I don't have much
choice. However, I'll try finding an identical-looking font and see if that
poses the same problems - and whether anyone notices the difference!

Gary

 

[Gary McGill]

Aaaaargh!

When I was trying last week to put together an example of my problem, using
some of the standard fonts that come with Word, I couldn't get the problem
to appear. This was annoying, since it seemed like the problem only appeared
when I didn't want it to.

Today, I'm trying to use those standard fonts "for real", and (surprise!)
the problem appears when using those fonts too! Someone, somewhere has it in
for me!

Anyway, that has at least let me create a reproducible example - I've put an
updated sample at http://www.electrum.co.uk/gary/chart that uses the Eras
Light ITC font that comes with Office 2000.

Of course, probably no-one is persevering with this thread by now...

Gary