Copying an Excel ChartObject into a Word Template - how to?

[Paul Willman]

I have a VB6 application that creates a Word document by pasting text from a
database into different places in the document by means of bookmarks in a
template. My newest challenge is to insert 2 Graphs from Excel into the end
of the Word document. Here are my problems:

1) Is there a paste option to make the graph be scaled to the size of the
printed sheet? Currently, when I paste it, the graph is huge and I have to
manually resize it in the Excel file before copy/pasting it into Word. This
leads to my next challenge

2) Simply setting the .Width and .Height attribute of the chart object for
the graph doesn't appear to cut it. That appears to just control the space
the graph has to draw in. The bars get cramped and the legend doesn't scale
at all, getting cut off in the smaller graph. What is the best way to resize
a Graph in Excel?

3) When I paste the graph into the Word document, the graph always gets
inserted at the top of the document, even though I'm using bookmarks located
at the end of the document when I paste. What is the best way to control the
location of a graph inserted into a Word Document?

Here is the code that I currently use to perform the resize/copy/paste. I
use locally-scoped constants for the width and height of the target graph
size.

'Resize the First Graph
wkbNarrMetric.Worksheets(1).ChartObjects(1).Width = cintGraphWidth
wkbNarrMetric.Worksheets(1).ChartObjects(1).Height = cintGraphHeight

'Copy the First Graph to the Clipboard
wkbNarrMetric.Worksheets(1).ChartObjects(1).Copy

'Paste the First Graph into the Narrative
docNarr.Bookmarks(16).Select
docNarr.Bookmarks(16).Range.Paste

'Resize the Second Graph
wkbNarrMetric.Worksheets(2).ChartObjects(1).ScaleWidth 0.7, , msoFalse
wkbNarrMetric.Worksheets(2).ChartObjects(1).Height = cintGraphHeight

'Copy the Second Graph to the Clipboard
wkbNarrMetric.Worksheets(2).ChartObjects(1).Copy

'Paste the Second Graph into the Narrative
docNarr.Bookmarks(17).Select
docNarr.Bookmarks(17).Range.Paste

Thanks for the help. I realize I've snuck an Excel question into the Word
group, and for that I do apologize.

Paul

 

[Ed]

Hi, Paul.

I'm not the real expert on these things by any means, but here's a couple of
things you might want to explore.

If you're not going to have to change the chart after you paste it in, you
might try the Excel CopyPicture VBA command. The code I use in a macro is
..CopyPicture xlPrinter, , xlScreen
Your Paste command, then, will paste a graphic object, rather than an Excel
Workbook object, which Word may treat differently.

For how to get objects to stay where you paste them, you should probably
search the word.drawing.graphics NG. You might get a bit more control using
Word's PasteSpecial method. Word also has a PasteAndFormat method with a
wdRecoveryType argument - I have never used this, but it looks like it might
have some options that could be helpful.

Ed

 

[Paul Willman]

Hi Ed,

Thanks for the feedback. When I tried the CopyPicture method with the
parameters you specified, the thing barfed. However, when I tried it without
the parameters, it worked like a charm! Even the placement of the images
using the bookmarks began working.

Thanks for the help!

Paul

 

[Ed]

Just passing on some of the excellent help I've gotten here. Glad it worked
for you.
Ed