PasteSpecial Picture in Defined Section

[David Squirrell]

I am need to insert a graph from Excel into Section 2 of a Word document. Just before the graph I need to insert a title for the graph, say Graph 1. Once I get this to work correctly I need to amend the existing code so that a series of between 5 and 12 graphs are generated, with titles Graph 1, Graph 2, etc.

The problem I have is that when I paste special the graph into Section 2 it defaults to the beginning of the section, even though I am using the collapse method.

I have tried for two days to resolve this so would be exceptionally grateful for any assistance that resolves the problem.

The code I am using is:

Set WordApp = GetObject(, "Word.Application")

' Reference active document (Document needs to be open already!)
Set WordDoc = WordApp.ActiveDocument

' Copy chart as a picture
ActiveSheet.ChartObjects("Chart 99").Chart.CopyPicture _
Appearance:=xlScreen, _
Size:=xlScreen, _
Format:=xlPicture

' Heading for first graph
WordDoc.Sections(2).Range.InsertAfter ("Graph 1") & Chr(12)

WordDoc.Sections(2).Range.Collapse Direction:=wdCollapseEnd

' Paste chart in Section 2
WordDoc.Sections(2).Range.PasteSpecial _
Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False


Submitted via EggHeadCafe - Software Developer Portal of Choice
Database Scale-Out Strategies for Web Apps
http://www.eggheadcafe.com/tutorial...95-c0f6ed092c90/database-scaleout-strate.aspx

 

[Pesach Shelnitz]

Hi David,

Instead of trying to collapse the Range object held in the Range property of
a Word section, create your own Range object, set it to the Range object of
the section before each call to PasteSpecial, collpase it, and use it to
call PasteSpecial, as in the following.

Set MyRange = WordDoc.Sections(2).Range
MyRange.Collapse Direction:=wdCollapseEnd

' Paste chart in Section 2
MyRange.PasteSpecial _
Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False

Also, I'm wondering if really want to use Chr(12) after the caption. Chr(12)
inserts a page break in Word. Thus, you will have a page break between each
caption and chart. I suggest that you replace it by vbCrLf. If you want a
page break before the caption, add it before the caption.