How do I Paste an Excel table into word without losing the row heights

user_9374675 0
Viewed 149

I am copying a range from Excel into Word in the following way:

Set oRng = ActiveSheet.Range("B2:V27")
oRng.Copy
objDoc.ActiveWindow.Selection.PasteSpecial , , 0

This works except that the row heights are changed.

Doing it manually, row heights can be kept with paste special as Microsoft Excel Worksheet Object

enter image description here

My question is: Can this be done in code?

ms-word vba excel
1 Answers

This works for me

Option Explicit

Const wdPasteOLEObject As Integer = 0
Const wdInLine As Integer = 0

Sub Sample()
    Dim oWordApp As Object, oWordDoc As Object

    On Error Resume Next
    Set oWordApp = GetObject(, "Word.Application")
        
    If Err.Number <> 0 Then
        Set oWordApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oWordApp.Visible = True
    
    Set oWordDoc = oWordApp.Documents.Add
    
    '~~> Sample range copied
    Sheet1.Range("A1:D10").Copy
    
    oWordDoc.ActiveWindow.Selection.PasteSpecial Link:=False, _
                                    DataType:=wdPasteOLEObject, _
                                    Placement:=wdInLine, _
                                    DisplayAsIcon:=False
End Sub

enter image description here

user_11405790
Related