Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / October 2006

Tip: Looking for answers? Try searching our database.

VBA Code - Pasting Pictures from Excel into Word

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Cush - 25 Oct 2006 13:17 GMT
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 - 25 Oct 2006 15:24 GMT
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
[quoted text clipped - 8 lines]
> after it's pasted.  I'm working in Excel and Word version 11 (2003)
> on XP.

What code are you using?

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Cush - 25 Oct 2006 16:18 GMT
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...

> Cush was telling us:
> Cush nous racontait que :
[quoted text clipped - 13 lines]
>
> What code are you using?
Jean-Guy Marcil - 25 Oct 2006 18:36 GMT
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:
[quoted text clipped - 16 lines]
> 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...

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Cush - 25 Oct 2006 19:54 GMT
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

> Cush was telling us:
> Cush nous racontait que :
[quoted text clipped - 67 lines]
> With object you never need to use the selection object and actually have the
> code go from one window to the other...
Jean-Guy Marcil - 25 Oct 2006 21:05 GMT
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?
[quoted text clipped - 6 lines]
> 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

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org


Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.