VBA Excel to Word automation not holding formatting

Question

Good day

I have built an add-in that assists me to transfer ranges, Sheets and Entire Workbooks from Excel to Word on the click of a button. It works beautifully, but when the Excel range changes in certain cases, the Word formatting goes completely whack. The Word tables are also formatted after the fact, but then when we refresh the data the Word formatting also jumps wider than the page or goes completely out of sync with the changes in both Word and Excel.

Does anyone know how to:

1. Copy-paste this information so that "Preserve formatting after update" is automatically on on the Word Linked Worksheet Object?

2. Have links not automatically update?

3. More seemlessly transfer the information without having all the compatibility issues that exists between Excel and Word?

Some of my code I use:

Sub ExcelRangeToWord(ByVal control As IRibbonControl)

Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table
Dim newDoc As Boolean

newDoc = False

'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False

'Copy Range from Excel
  Set tbl = Selection

'Create an Instance of MS Word
  On Error Resume Next

    'Is MS Word already open?
      Set WordApp = GetObject(class:="Word.Application")

    'Clear the error between errors
      Err.Clear

    'If MS Word is not already open then open MS Word
      If WordApp Is Nothing Then
        Set WordApp = CreateObject(class:="Word.Application")
        newDoc = True
      End If

    'Handle if the Word Application is not found
      If Err.Number = 429 Then
        MsgBox "Microsoft Word could not be found, aborting."
        GoTo EndRoutine
      End If

  On Error GoTo 0

'Make MS Word Visible and Active
  WordApp.Visible = True
  WordApp.Activate

'Create a New Document or set ActiveDocument
  If newDoc Then
    Set myDoc = WordApp.Documents.Add
  Else
    Set myDoc = WordApp.ActiveDocument
  End If

'Copy Excel Table Range
  tbl.Copy

'Paste Table into MS Word
    WordApp.Selection.PasteExcelTable _
    LinkedToExcel:=True, _
    WordFormatting:=False, _
    RTF:=True

'Autofit Table so it fits inside Word Document
  Set WordTable = myDoc.Tables(1)
  WordTable.AutoFitBehavior (wdAutoFitWindow)

EndRoutine:
'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

As well as:

Sub CopyActivesheetToWord(ByVal control As IRibbonControl)
    Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet

    Application.ScreenUpdating = False
    Application.StatusBar = "Creating new document..."

    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Add
    Set ws = ActiveWorkbook.ActiveSheet
    With ws
        Application.StatusBar = "Copying data from " & .Name & "..."
        .UsedRange.Copy
        On Error Resume Next
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=True
        Set WordTable = wdDoc.Tables(1)
        WordTable.AutoFitBehavior (wdAutoFitWindow)
        Application.CutCopyMode = False
    End With
    Set ws = Nothing

    Application.StatusBar = "Cleaning up..."
    With wdApp.ActiveWindow
        If .View.SplitSpecial = wdPaneNone Then
            .ActivePane.View.Type = wdPrintView
        Else
            .View.Type = wdPrintView
        End If
    End With

    Set wdDoc = Nothing
    wdApp.Visible = True
    wdApp.Activate
    Set wdApp = Nothing

    Application.StatusBar = False
End Sub

Thank you

Answer 1

The resizing of linked workbooks is by design, but there is a registry edit to prevent it. With Word closed, start the Registry Editor and navigate to:

• Word 2007 - HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
• Word 2010 - HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Options
• Word 2013 - HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Excel\Options
• Word 2016 - HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options

Add a new DWORD value:

QFE_Boston

Set the new DWORD value to 1

You could, of course, apply the registry edits with VBA, but you'd need to test the Word version to know which key to edit.

As for your numbered questions:

1. Presumably you're referring to the addition of the \* MERGEFORMAT switch to the LINK field's code. That is unnecessary for such fields unless you're reformatting them post-insertion, in which case I'd expect Word to add the switch automatically, but can be done by editing the field code - which you can do with VBA.
2. Remove the \a switch from the field code or uncheck Word's 'Update automatic fields at open' option- which you can also do with VBA. Note that neither has any effect if the workbook is open.
3. You will need to clarify what you mean. I am not aware of any 'compatibility' issues between Excel and Word.