Preserve Formatting on Update

Squarecat · September 29th, 2006, 03:44 AM

Hi all;

Hope someone can help with this one...

I've created code to import 8 tables from a specified spreadsheet as linked objects. The code then goes on to format the tables and title and footnote text. This means that the Excel document has different formatting to the Word doc so on update the formatting is taken from Excel and wipes my formatting in Word.

There is an option in Word (Edit > Links) "Preserve formatting on update", which works a treat but I can't work out how to do it with VBA code.

It is possible to do it with Shapes and Inline shapes:

Sub PreserveFmtg()
ThisDocument.Shapes(1).OLEFormat _
.PreserveFormattingOnUpdate = True
End Sub

But does anyone know of a method to do it with tables?
Thanks

Squarecat · September 29th, 2006, 08:33 AM

Just to let you know - I fixed this myself!
If you're interested:

OLD CODE:

Code:

'import Table + source
     oXL.Sheets("Key financials").Select
            oXL.Range("A8:H20").Select
            oXL.Selection.Copy
     With oWordDoc.ActiveWindow
         Selection.PasteExcelTable True, False, False
     End With

NEW CODE:

Code:

'import Table + source
     oXL.Sheets("Key financials").Select
            oXL.Range("A8:H20").Select
            oXL.Selection.Copy
     With oWordDoc.ActiveWindow
         Selection.PasteExcelTable True, True, False
     End With

The only change here is the second variable on the PasteExcelTable which has changed from False to True - this forces preserving Word formatting chnages rather than updating from the Excel sheet.

Just for interest the first variable sets whether the object is linked, the second whether to preserve formatting, and the third if False pastes as HTML - True pastes as RTF.

Thanks