copy/paste excel chart to specific page in Word document

[intoit]

Hi,

I'm using Office 2003. Using VBA, I'd like to copy and paste an excel chart
onto
page 6 of a Word document/template. The code below copies and pastes the
excel chart to page 1.

Any ideas what I have to add to:
A) specify the page
B) specify the position of the chart on the page, and
C) give the chart a 'name' that can be referenced later within the macro
(i.e., rather than some arbitrary number that Word might give it).

Thanks for any ideas.

Sub copy_charts_Word()

Dim role_chart_sh As Shape

Dim MSW As Word.Application
Dim MSWfile As Word.Document

Set MSW = New Word.Application
MSW.Visible = msoCTrue
Set MSWfile = MSW.Documents.Open("C:\Report_template.doc")

Set role_chart_sh = Worksheets("Chart_Role").Shapes(1)
role_chart_sh.CopyPicture Appearance:=xlScreen, Format:=xlPicture

MSW.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture,_
Placement:=wdInLine, DisplayAsIcon:=False

End Sub

 

[That Guy]

Hi,

I'm using Office 2003. Using VBA, I'd like to copy and paste an excel chart
onto
page 6 of a Word document/template. The code below copies and pastes the
excel chart to page 1.

Any ideas what I have to add to:
 A) specify the page
 B) specify the position of the chart on the page, and
 C) give the chart a 'name' that can be referenced later within the macro
 (i.e., rather than some arbitrary number that Word might give it).

 Thanks for any ideas.

 Sub copy_charts_Word()

 Dim role_chart_sh As Shape

 Dim MSW As Word.Application
 Dim MSWfile As Word.Document

 Set MSW = New Word.Application
 MSW.Visible = msoCTrue
 Set MSWfile = MSW.Documents.Open("C:\Report_template.doc")

 Set role_chart_sh = Worksheets("Chart_Role").Shapes(1)
 role_chart_sh.CopyPicture Appearance:=xlScreen, Format:=xlPicture

 MSW.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture,_
 Placement:=wdInLine, DisplayAsIcon:=False

 End Sub

So you want to put it on page 6? Read this article: http://daiya.mvps.org/wordpages.htm.
It explains how word does not think of the documents you write while
using it in terms of pages. Therfore the easiest way to do this is to
separate your document into sections using breaks. Then it wouldn't
matter if there were 5 or 50 pages ahead of the section you want your
chart in because you are addressing a specific section not a page.

That being said I would suggest that you add a section in between the
main part of the document and the chart, and then again after the
chart so that you can address the section properly. After doing that
you would then use:

ActiveDocument.Sections(x).Range.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, Placement:=wdInLine,
DisplayAsIcon:=False

x of course being the section for the chart. This should put the shape
in the range of the designated section.

Now as for referring to the shape later in the program use the shapes
name property and set it to whatever you want then when you are
addressing it later you can refer to it using shapes("name").

Hope that clears a few things up.