VBA Keeps a Link to Exel Table When Exporting to Word-CodePudding

I use the following code to copy a range from Excel to Word. The range contains the exact content with all the formatting and line spacing (row heights to be more specific). In order to keep the row heights I use the below code. However, when I paste it using the below code, it also keeps a link to the excel table when double clicking on Word it goes brings up an Excel editor in Word.

What I want to do is to keep the content formatting (as in Excel) and export it to Word without a link to the Excel table. If I don't use "DataType:=wdPasteOLEObject" Word chooses its own row height.

Sub ExportRangeToWord()
    Set obj = CreateObject("Word.Application")
    obj.Visible = True
    Set newObj = obj.Documents.Add
    Sheets("FinalContent").Activate
    Sheets("FinalContent").UsedRange.Copy
    newObj.Range.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement:=wdInLine, DisplayAsIcon:=False
    Application.CutCopyMode = False
    obj.Activate
    Sheets("MainSheet").Activate
End Sub

Any idea how to remove the link although it says "Link:=False".

CodePudding user response：

For example:

Sub ExportRangeToWord()
Dim wdApp As New Word.Application, WdDoc As Word.Document
With wdApp
  .Visible = True
  Set WdDoc = .Documents.Add
  Sheets("FinalContent").UsedRange.Copy
  WdDoc.Range.PasteAndFormat Type:=wdFormatOriginalFormatting
  ' or:
  'WdDoc.Range.PasteExcelTable LinkToExcel:=False, WordFormatting:=False, RTF:=False
  WdDoc.Activate
  Application.CutCopyMode = False
End Sub

Note that the code includes two possible approaches to pasting the data.