VBA Code - Pasting Pictures from Excel into Word

[Cush]

I am using Excel to automate the creation of a simple report in Word. Due to
limited stack space I've had to copy my charts in Excel as pictures. When I
then try to copy and paste these pictures into a Word document as part of the
automation, the picture doesn't stay where I pasted it in the report.

Does anyone have the code that can be used in Excel to paste a picture in
Word and have it stay in the same place? The other picture formatting (ex.
wordwrap) specifications seem to work properly. I'm just having problems
with the picture moving with the text written after it's pasted. I'm working
in Excel and Word version 11 (2003) on XP.

Thanks.

 

[Jean-Guy Marcil]

Cush was telling us:
Cush nous racontait que :

I am using Excel to automate the creation of a simple report in Word.
Due to limited stack space I've had to copy my charts in Excel as
pictures. When I then try to copy and paste these pictures into a
Word document as part of the automation, the picture doesn't stay
where I pasted it in the report.

Does anyone have the code that can be used in Excel to paste a
picture in Word and have it stay in the same place? The other picture
formatting (ex. wordwrap) specifications seem to work properly. I'm
just having problems with the picture moving with the text written
after it's pasted. I'm working in Excel and Word version 11 (2003)
on XP.

What code are you using?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Cush]

I'm using Excel VBA and the code to copy the picture and paste it into Word is:

appWord.Selection.TypeParagraph
AppActivate "Microsoft Excel"
Sheets("Congress_Charts").Select
ActiveSheet.Shapes("Report1 - Pic01").Select
Selection.Copy
appWord.Selection.ParagraphFormat.Alignment = 1
appWord.Selection.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, _
Placement:=wdFloatOverText, DisplayAsIcon:=False
appWord.Selection.MoveRight Count:=1
appWord.Selection.TypeText _
"We need to invest in high schools..."

This code works for Word v.9.0, but doesn't work for Word v.11.0. When I
use this code in version 11 the pictures move with the text that follows the
pictures. Thanks for your insights...

 

[Jean-Guy Marcil]

Cush was telling us:
Cush nous racontait que :

I'm using Excel VBA and the code to copy the picture and paste it
into Word is:

appWord.Selection.TypeParagraph
AppActivate "Microsoft Excel"
Sheets("Congress_Charts").Select
ActiveSheet.Shapes("Report1 - Pic01").Select
Selection.Copy
appWord.Selection.ParagraphFormat.Alignment = 1
appWord.Selection.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, _
Placement:=wdFloatOverText, DisplayAsIcon:=False
appWord.Selection.MoveRight Count:=1
appWord.Selection.TypeText _
"We need to invest in high schools..."

This code works for Word v.9.0, but doesn't work for Word v.11.0.
When I
use this code in version 11 the pictures move with the text that
follows the pictures. Thanks for your insights...

Make sure you do not use the selection object.
Set a range to the paragraph you want to insert the floating picture,
then set a range to a paragraph below where the freshly pasted picture is
anchored and add your text there.

Dim appWord As Word.Application
Dim rgePaste As Word.Range

'Not how you did it from Excel...
'But I tested in Word...
Set appWord = Word.Application
Set rgePaste = appWord.Selection.Range

With rgePaste
.InsertParagraph
.Collapse wdCollapseStart
End With

AppActivate "Microsoft Excel"
Sheets("Congress_Charts").Select
ActiveSheet.Shapes("Report1 - Pic01").Select
Selection.Copy

With rgePaste
.ParagraphFormat.Alignment = 1
.PasteSpecial , False, wdFloatOverText, False, _
wdPasteMetafilePicture
Set rgePaste = .Next.Paragraphs(1).Range
End With
With rgePaste
.Collapse wdCollapseStart
.Text = "We need to invest in high schools..."
End With

Also, in Excel you should not need to use AppActivate and Select.
Use a shape object to refer to your picture and use that instead:

Dim wksTarget As Worksheet
Dim shpTarget As Shape

Set wksTarget = ActiveWorkbook.Worksheets(1)
Set shpTarget = wksTarget.Shapes(1)
shpTarget.Copy


It is much more efficient and much less error-prone to use objects.
With object you never need to use the selection object and actually have the
code go from one window to the other...

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Cush]

I got the code you sent to me to work in Excel VBA, but have two follow up
questions:

1) How do I set a range where the picture should be pasted?
2) How do I anchor a picture so that it doesn't move as further paragraphs
are written?

The code you sent allowed me to copy and paste the shape and text more
efficiently, but I don't know how to set the range for where the picture is
to be located and also how to anchor the picture.

My biggest problem is that in the "Format Picture" options written in the
VBA code, I can't get the "Move Object with Text" to be False, and for the
"Lock Anchor" to be True.

Thanks

 

[Jean-Guy Marcil]

Cush was telling us:
Cush nous racontait que :

I got the code you sent to me to work in Excel VBA, but have two
follow up questions:

1) How do I set a range where the picture should be pasted?

??
Well, how do you tell you code where to place the picture?
What is the intended location?

2) How do I anchor a picture so that it doesn't move as further
paragraphs are written?

The code you sent allowed me to copy and paste the shape and text more
efficiently, but I don't know how to set the range for where the
picture is to be located and also how to anchor the picture.

My biggest problem is that in the "Format Picture" options written in
the VBA code, I can't get the "Move Object with Text" to be False,
and for the "Lock Anchor" to be True.

Locking the anchor is easy, but there is no such thing as a method or
property for "Move Object with Text". For that, you have to use the relative
position property.
Here is a Word example you can adapt to your needs:

Dim shpToPaste As Shape
Dim rgeToPaste As Range

Selection.Collapse wdCollapseStart
Set rgeToPaste = Selection.Range

rgeToPaste.PasteSpecial , False, wdFloatOverText, False, _
wdPasteMetafilePicture

Set rgeToPaste = rgeToPaste.Paragraphs(1).Range
Set shpToPaste = rgeToPaste.ShapeRange(1)

With shpToPaste
.LockAnchor = True
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.RelativeVerticalPosition = wdRelativeVerticalPositionPage
End With

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org