It turns out an auto_open routine with a switch to of ActivePrinter to the
Microsoft Document Imaging print driver seems to work in getting around
this.

Robin Hammond
www.enhanceddatasystems.com


"Robin Hammond" wrote in
message ...
Jon and Dave,

I'm very intersted in the problem with loss of colours on the paste
metafile to Word issue. In a production/commercial environment, with the
"DTP/report production" machines running XP pro SP2, I am seeing this
issue where greys become blacks, reds become blacks, etc., and it is
driving them nuts. In short, we have working code on some machines (most
running SP1) but once it gets to the people who actually fine tune the
word docs for print, it all goes wrong.

We've been trying to figure out the build differences for a week, and will
try the printer trick tomorrow morning, but if you have any further
thoughts I would very much like to hear them.

--
Robin Hammond
www.enhanceddatasystems.com


"Jon Peltier" wrote in message
...

"Dave M" <Dave wrote in message
...
Found another chart bug while running my 3800-line data visualization
Excel/VBA program today. In the course of six years fiddling with VBA
in
Excel, I've concluded that programmatic acess to charts is by far the
biggest
source of trouble in Excel/VBA programming. Since I can't find any
other
Internet-based venue for submission of bugs to Microsoft, I'll post this
bug
here. But first, I'll share a few of my other discoveries of weird
chart
behavior, so that others will not be driven to beat their heads against
their
desks as I have done.

Tip 1:
The size in screen coordinates of axis titles is incorrect until the
axes
are actually drawn on-screen. This means that screen updating must be
enabled before getting these sizes.

The size of axis titles, chart titles, and data labels is not even
exposed to VBA. But there are workarounds.

Tip 2 (the Golden Rule):
You can't work with chart series if the series is not visible (as is the
case if the linked cells are all empty, for example). Golden rule: set
chart.DisplayBlanksAs=xlZero before changing the datasets of a chart
series.

This is true of certain properties of marker-based chart series (i.e.,
line and XY, not column, bar, area). Rather than DisplayBlanksAs, which
is not a reliable workaround, temporarily convert the series to area
while working on it, then convert it back.

Tip 3:
In general, you can't programmatically access chart elements that can
not be
displayed (for example, you can't set the title of a completely empty
chart).

Not surprising, is it? A completely empty chart has no title.

Tip 4:
When moving the PlotArea within the ChartArea (by setting position of
the
top left corner), don't allow the bottom or left edges of the PlotArea
to be
moved outside the confines of the ChartArea. If you try, an error will
be
raised.

You get an error? In my experience (and in the test I just ran) the plot
area moves or resizes only as far as it will go, without an error.

And now, today's bug. When applying a Name to a ChartObject, strange
things
happen if that name contains any punctuation except for the underscore,
or
contains certain, but not all, of the extended ASCII characters, or
contains
a space. If you provide such a name then you will find that that
particular
chart is NOT RETURNED when iterating over the ChartObjects collection by
using the For Each mechamism, even though ChartObjects.Count will show
the
correct number of charts. In fact, in a running program,you can add a
watch
on ChartObjects, expand it, and see that Count is correct, whereas the
listed
Chart nodes will be missing the ones that were assigned names as
described
above. The solution is to address each chart by its index, using
Chartobjects.Count and a traditional For loop. Very weird.

Another solution is to use recognized characters in the chart object
name. You need to watch out what characters are used in sheet names and
file names too.

Okay, one more bug workaround I'm dying to share. When pasting Office
tables, charts, graphics, etc. as metafiles, one often finds that
colored
text, and certain other colored graphical elements, are shown as black
in the
pasted metafile graphic. Fix this problem by pretending to print to a
color
printer before doing the cut and paste (go through the print dialogs,
selecting a color printer, then cancel the print dialog).

I've never seen this as I'm always connected to a color printer.

I'm using Excel 2003 SP2, Excel 2002 SP2 or 3 (I forget which), and Excel
2000 SP3. If you think any of these are strange, wait until you try Excel
2007.

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