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

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
Wed Nov 01 07:51:57 CST 2006

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" <PaulWillman@discussions.microsoft.com> wrote in message
news:3D9A64EC-9834-4064-B77A-BC2DD0AB37B8@microsoft.com...
>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

PaulWillman
Wed Nov 01 17:25:02 CST 2006

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" wrote:

> 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" <PaulWillman@discussions.microsoft.com> wrote in message
> news:3D9A64EC-9834-4064-B77A-BC2DD0AB37B8@microsoft.com...
> >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
Thu Nov 02 07:34:09 CST 2006

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

"Paul Willman" <PaulWillman@discussions.microsoft.com> wrote in message
news:0BE9A0BA-1ADC-4295-90BB-967F9F88ACD7@microsoft.com...
> 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" wrote:
>
>> 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" <PaulWillman@discussions.microsoft.com> wrote in message
>> news:3D9A64EC-9834-4064-B77A-BC2DD0AB37B8@microsoft.com...
>> >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
>>
>>
>>