when copying a range from excel and pasteSpecial to word I want to add a border line around the picture

Question

I have two questions. 1 In word I can go to a bookmark "input", but I want to keep that one and paste my selection in a new bookmark one line below this one. I tried .selection.Collapse Direction:=wdCollapseEnd to go to the end and then insertLine but that does not do the trick. Also tried ActiveDocument.Bookmarks.Add "Excel1", newRange before pasting (see commented part of code) but also no luck. 2 When I copy a range from excel to word with DataType:=wdPasteEnhancedMetafile the left border is not visible in word (the other borders are fine. How to add the left border after pasting the metafile Picture ?

' in excel
selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
          lWidth = selection.Width
          lHeight = selection.Height
wdDoc.Activate
' With wdApp
''     Set prevRange = wrdDoc.Bookmarks("input").Range
 '   Set newRange = prevRange   'Have to set the range to something initially
 '   newRange.SetRange prevRange.End, prevRange.End
 '   ActiveDocument.Bookmarks.Add "Excel1", newRange
 'End With
  
     With wdApp
        .Visible = True
          .selection.Goto What:=wdGoToBookmark, Name:="input"
          .selection.Collapse Direction:=wdCollapseEnd
          'leave the bookmark input as input and make a new bookmark on next word line for the picture.
          .selection.InsertLine
          .selection.PasteSpecial Link:=False, DisplayAsIcon:=False, _
            DataType:=wdPasteEnhancedMetafile, Placement:=wdInline
          'add a line at the left border of metafile ??
          '.Selection.InsertAfter Chr(13)
          .selection.InsertBreak (wdPageBreak)
    End With

nextXL:

score 0 · Accepted Answer · answered Mar 04 '21 at 21:58

For the first issue, try something along the lines of:

Sub Demo()
Dim xlSht As Excel.Worksheet
Dim wdApp As Word.Application, wdDoc As Word.Document, wdRng As Word.Range
Dim StrDocNm As String, StrBkMk As String
Set xlSht = ActiveSheet
StrBkMk = "BkMk1"
Set wdApp = GetObject(, "Word.Application")
With wdApp
  'Point to the active document
  Set wdDoc = .ActiveDocument
  With wdDoc
    'Paste the Excel data at the designated bookmark
    If .Bookmarks.Exists(StrBkMk) Then
      xlSht.Range("C2:E7").Copy
      Set wdRng = .Bookmarks(StrBkMk).Range
      wdRng.Characters.Last.Next.InsertBefore vbCr
      wdRng.PasteSpecial Link:=False, DisplayAsIcon:=False, _
        DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine
      .Bookmarks.Add StrBkMk, wdRng
      xlSht.Range("C9:E15").Copy
      With wdRng
        .End = .End + 1
        .Collapse wdCollapseEnd
        .PasteSpecial Link:=False, DisplayAsIcon:=False, _
          DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine
      .Bookmarks.Add "BkMk2", .Duplicate
      End With
    End If
  End With
End With
Set wdRng = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End Sub

Note that there is no need to Select anything in Word or Excel. Note also that I haven't included any code for opening the document, etc. I assume you already have that sorted.

For the second issue, it would appear the border has been applied to the right side of the adjacent cell(s), not to the left side of the copied cell(s). I'd suggest correcting that at the source.

when copying a range from excel and pasteSpecial to word I want to add a border line around the picture

1 Answers1