CODEWITHSUNDEEP
vbaexcelms-word
H. Keyser
H. Keyser

Reputation: 43

Select a VBA Pasted Shape from Excel to Word so I can wrap text tight

I'm developing a spreadsheet to automatically generate quotes; this involves copying the correct diagram (a grouped Excel shape) from a sheet with a library of our possible diagrams, and pasting/inserting/[whatever will work best] that image into the VBA-assembled Word document.

So far, I am able to successfully identify, copy and paste special the correct diagram shape from Excel into Word. However, every technique I have found means that the shape either wraps text as inline or infront-- whereas I need the next portion of text to be directly aside the diagram-- thus "wdTight"

Below, you can see how I paste in the current shape. But I can't figure out how to make that shape into ".wrapFormat.Type = wdTight"

I am generally met with a series of Runtime 438 errors: object doesn't support this property or method.

I have a theory that this is because Word considers the selection to always be the blinking text cursor, and not the just pasted shape which is activated and has a hovering layout options tooltip (but that is just a theory).

Here is the code. Please help.

Sub export_excel_to_word()
Dim appWord
Dim quoteWord
Dim wordSelection

Set appWord = CreateObject("Word.Application")
    appWord.Visible = True
Set quoteWord = appWord.Documents.Add
Set wordSelection = appWord.Selection

'This part works
Module1.ImagesRefresh

   'Product 1 image insertion
    Sheets("Quick Lookup").Shapes("QuoteProduct1Image").Copy
    wordSelection.PasteSpecial Link:=False, _
      DataType:=wdPasteShape, _
      Placement:=wdInLine, _
      DisplayAsIcon:=False

'This part does not work and is where I need help
    With wordSelection
     .WrapFormat.Type = wdWrapFront
    End With

Upvotes: 4

Views: 2864

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Try creating a variable to represent the newly pasted shape, it should be accessible by index, and should be the last index:

   'Product 1 image insertion
    ActiveSheet.Shapes(1).Copy
    wordSelection.PasteSpecial Link:=False, _
      DataType:=wdPasteShape, _
      Placement:=wdInLine, _
      DisplayAsIcon:=False

    Dim wdShape As Word.Shape
    Set wdShape = quoteWord.Shapes(quoteWord.Shapes.Count)

    wdShape.WrapFormat.Type = wdWrapTight

Without using the shape variable, this might also work, but I have not tested against a document with several shapes (assuming you're adding shapes in sequence/iteration)

   'Product 1 image insertion
    ActiveSheet.Shapes(1).Copy
    wordSelection.PasteSpecial Link:=False, _
      DataType:=wdPasteShape, _
      Placement:=wdInLine, _
      DisplayAsIcon:=False

    wordSelection.ShapeRange(1).WrapFormat.Type = wdWrapTight

If you're also inserting text (e.g., through the TypeText method of a Word.Range, try the code below. I'm not super-familar with Word automation, and the rule that you should avoid activate/select I think is less strict in Word (or has more exceptions) but as a rule of thumb, I'd still try to avoid reliance on the Selection object, and work with ranges instead.

What I'm doing here is pasting to the wordSelection.Range rather than the Selection itself. Then I do

ActiveSheet.Shapes(1).Copy
wordSelection.Range.PasteSpecial Link:=False, _
  DataType:=wdPasteShape, _
  Placement:=wdInLine, _
  DisplayAsIcon:=False
Dim wdShape As Word.Shape
Set wdShape = quoteWord.Shapes(quoteWord.Shapes.Count)
wdShape.WrapFormat.Type = wdWrapTight

wordSelection.TypeText "some text"

enter image description here

Upvotes: 3

Related Questions