I have written a macro to optimize the routine modifications (changing font, row height, size, etc.) I always make for the tables linked from Excel to Word. Now the problem is that when I update the tables (via right click/update Link), all the macro modifications are undone. Is there a remedy for this problem? (Note: the same doesn’t happen if the modifications are made manually (without macro). I use Word 2016. I have found this problem is not specific to my macro and is the case for every macro running on linked Word tables. See the following simple example which just autofits a table and determines its height). I have also posted this question on SuperUser and Quora. (Cross posting for not getting a satisfactory answer)

Sub autofit()



' autofit Macro

Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)
Selection.Tables(1).Rows.Height = CentimetersToPoints(0.01)

End Sub

Anything you do in Word to refresh a field - which includes the LINK fields that are used when Word sources data from Excel - will erase any formatting you have applied to the field's result. The behaviour is by design and cannot be overridden.

Contrary to what you say, it doesn't matter whether the change is made via a macro or manually. Since your macro doesn't do anything to refresh the link, I cannot see how that would affect any other formatting you have applied to the table. That said, I cannot see the point of setting the row heights to 0.1mm...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Rather than a macro solution, or as part of a macro solution, look into styles and table styles. Use the tools that are built into Word.


You can use a CHARFORMAT switch on fields to help them maintain formatting.

A Charformat switch isn't going to do anything useful in a LINK field where a variety of formats are required. Nor will table Styles. About the only thing that will work is applying the required format to the source.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]