RECrerar
08-18-2009, 02:47 AM
Hi
I was wondering how to stop automatic updating of links. I have a macro that imports tables from Excel and then formats them to fit the page width and contents.
When the tables are updated they lose their formatting and so I have written another macro to update the links and fix the formatting. The linked tables are set to use the source formatting which I think makes things harder, but there is a fair bit of conditional formatting in the Excel sheet that I would like to keep and hence it seems simpler to keep their formatting and just adjust the column sizes in word whenever they are updated.
I have seen the following link which helps to disable the initial message http://www.vbaexpress.com/forum/showthread.php?t=26863&highlight=update+linked+tables
There are several tables and therefore I would like to be able to automatically set the link updating to manual.
Is it possible to use VBA to set link updating to manual?
Can I identify linked tables with VBA so that I don't try and update unlinked tables? (They may have different formatting requirements).
In the following code that I use for the updating I sometimes get the error "object deleted" on the formatting lines, any ideas why and how i prevent it?
Private Sub cbUpdate_Click()
' Loop through all table, update link and reformat
Dim tbl As Table
For i = 1 To ActiveDocument.Tables.Count
Set tbl = ActiveDocument.Tables(i)
Selection.Fields.Update
tbl.AutoFitBehavior (wdAutoFitWindow)
tbl.AutoFitBehavior (wdAutoFitContent)
Next i
Set tbl = Nothing
MsgBox "Updates and Formatting complete", vbOKOnly, "Success"
Unload Me
End Sub
I hope that makes sense and I really hope someone can help as I am rather stuck. If you need any more information, please do ask for it.
I was wondering how to stop automatic updating of links. I have a macro that imports tables from Excel and then formats them to fit the page width and contents.
When the tables are updated they lose their formatting and so I have written another macro to update the links and fix the formatting. The linked tables are set to use the source formatting which I think makes things harder, but there is a fair bit of conditional formatting in the Excel sheet that I would like to keep and hence it seems simpler to keep their formatting and just adjust the column sizes in word whenever they are updated.
I have seen the following link which helps to disable the initial message http://www.vbaexpress.com/forum/showthread.php?t=26863&highlight=update+linked+tables
There are several tables and therefore I would like to be able to automatically set the link updating to manual.
Is it possible to use VBA to set link updating to manual?
Can I identify linked tables with VBA so that I don't try and update unlinked tables? (They may have different formatting requirements).
In the following code that I use for the updating I sometimes get the error "object deleted" on the formatting lines, any ideas why and how i prevent it?
Private Sub cbUpdate_Click()
' Loop through all table, update link and reformat
Dim tbl As Table
For i = 1 To ActiveDocument.Tables.Count
Set tbl = ActiveDocument.Tables(i)
Selection.Fields.Update
tbl.AutoFitBehavior (wdAutoFitWindow)
tbl.AutoFitBehavior (wdAutoFitContent)
Next i
Set tbl = Nothing
MsgBox "Updates and Formatting complete", vbOKOnly, "Success"
Unload Me
End Sub
I hope that makes sense and I really hope someone can help as I am rather stuck. If you need any more information, please do ask for it.