Repeated header rows disappear after linked table is updated in Word

Question

I am trying to figure out how to prevent Word from removing the "repeat as header row at the top of each page" option when a linked table is update in Word. I have ~300 linked tables in one document from multiple data sources so it takes a lot of time to copy and paste the tables into the word document every time.

I have tried selecting the repeat row option in the page setup option in Excel, but that does not seem to work either.

Is there a way that I could either copy all of the tables into Excel programatically using VBA or to prevent Word from deleting the table properties every time the linked tables are updated?

Answer 1

OK. So I went ahead and wrote a macro that will save all of the HeadingFormat properties for each row in each table, update all tables and then reapply those formatting properties. The macro seems to work pretty well and should be usable for other applications. Enjoy!

Public Sub UpdateTables()
'Get Table Formats
Dim i As Integer
Dim j As Integer

Dim tablecount As Integer
tablecount = ActiveDocument.Tables.Count

Dim tableformats() As Integer
ReDim tableformats(tablecount)

For i = 1 To tablecount
    j = 1
    Do While ActiveDocument.Tables(i).Rows(j).HeadingFormat = -1
        tableformats(i) = tableformats(i) + 1
        j = j + 1
    Loop
    'MsgBox (tableformats(i))
Next i

'Update Table References
ActiveDocument.Fields.Update

'Apply Table Formating to Updated Tables
For i = 1 To tablecount
    For j = 1 To tableformats(i)
        If ActiveDocument.Tables(i).Rows(j).HeadingFormat = 0 Then
            ActiveDocument.Tables(i).Rows(j).HeadingFormat = wdToggle
        End If
    Next j
Next i       
End Sub