Set pasted link to update manually

[matt.courtney]

Hi All,

I've defined a macro to paste a cell from Excel into a Word document
as as link and have put a button on a custom toolbar.

The macro currently works but all the links it pastes are set to
update automatically which makes working with the Excel spreadsheet
very slow when the Word document is open.

I've been trying for days to figure out how to use VBA to set the link
to update manually - does anyone know how to do this?

Here's the macro I'm currently using:
Sub InsertExcelLink()
Selection.PasteSpecial Link:=True, DataType:=wdPasteText,
Placement:= wdInLine, DisplayAsIcon:=False
End Sub

I tried adding the following code to it to loop through all the links
after a new one is pasted and set them to update manually. Word ends
up compaining that it doesn't have enough memory and may not be able
to undo and changes it makes to the document. After the macro has
completed all the links are still set to automatically update.

Below is the field code that InsertExcelLink generates, if I could get
it to stop putting \a in there I'd be very happy
{ LINK Excel.Sheet.8 "\\\\server\\files$\\2008\\Pandora Road.xls"
"Report Contents!R173C11" \a \t \* MERGEFORMAT }

Thanks in advance for any suggestions.

 

[macropod]

Hi Matt,

Have you tried locking the field? That should prevent it from updating until you unlock it.

Before coding it, try it manually - select the object and use Ctrl-F11 to lock and Ctrl-Shift-F11 to unlock.

Cheers