Preserve formatting after update - Word VBA

[squarecat]

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

 

[CautionMP]

squarecat,
This should work for you.

Sub PreserveFmtgIS()
    ThisDocument.[b]InlineShapes[/b](1).OLEFormat _
        .PreserveFormattingOnUpdate = True
End Sub

If you want to apply this to all the objects you could use something like this:

Dim ishp As InlineShape
For Each ishp In ThisDocument.InlineShapes
  ishp.OLEFormat.PreserveFormattingOnUpdate = True
Next ishp

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[squarecat]

CMP,

Thanks for this but it doesn't seem to work for tables - I'm fine with it working for Shapes and Inline shapes but there deosn't seem to be a OLEFormtat as part of the tables collection.

Any other odeas!?
Thanks
squarecat

 

[CautionMP]

squarecat,
I need to take a step back, what code do you use to import 8 tables?

What type of objects are you creating when you insert the table?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[squarecat]

CMP,

Thanks for sticking with this one!

I've just worked it out - sorry to waste your time - but the answer is below.
OLD CODE:

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

NEW CODE:

'import Table + source
     oXL.Sheets("Key financials").Select
            oXL.Range("A8:H20").Select
            oXL.Selection.Copy
     With oWordDoc.ActiveWindow
         [red]Selection.PasteExcelTable True, [b]True[/b], False[/red]
     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 again for your help! ;-)
squarecat