excelvbams-wordresizeexport-to-word

How to copy a range in excel and then resize and paste in word with a macro


Sub CopyToWord()

    Dim objWord As New Word.Application


    'copying the range that I want to paste in Word
    With ThisWorkbook.Worksheets("grid_copy")
        .Range("b1:AA42").CopyPicture xlScreen

    End With

    'pasting the picture in a new Word document
    With objWord
        .Documents.Add
        .Selection.Paste
        .Selection.ShapeRange.Height = 651
        .Selection.ShapeRange.Width = 500
        'Those two lines don't work to resize the picture that i'm pasting in word
        .Visible = True
    End With


End Sub

The code is actualy working but I'm not capable of applying the resize of the image that I want. Do you guys know a way that I can resize the picture that i'm pasting in Word coming form a range in Excel?


Solution

  • Try:

    Sub CopyToWord()
    'copying the range that I want to paste in Word
    ThisWorkbook.Worksheets("grid_copy").Range("b1:AA42").CopyPicture xlScreen
    'pasting the picture in a new Word document
    Dim wdApp As New Word.Application, wdDoc As Word.Document, wdImg As Word.InlineShape
    With wdApp
      .Visible = True
      .ScreenUpdating = False
      Set wdDoc = .Documents.Add
      With wdDoc
        .Range.Paste
        Set wdImg = .InlineShapes(1)
        With wdImg
          .Height = 651
          .Width = 500
        End With
      End With
      .ScreenUpdating = True
    End With
    End Sub