Word Excel Link through Macro

[jkbob]

Help, I am new to word macros and am trying to automate links between
word and excel on a project that will be turned over to others with
even less experience then me. My project has about 200 links although
I am thinking of breaking them up into smaller word docs to reduce the
amount of links. I have found that by having the linked (source)
spreadsheet open, this has sped up the process. I have the links set
to manual update as the spreadsheet has a habit of continuously running
even when I haven't changed anything with updates set to automatic
(seems like a good way to kill a hard drive).

My problem is that this code, which was taken from another posts, seems
to loop continuously and ends up with an 'insufficient memory' message
which causes the process to hang. Interestingly enough, the same
results happens with only 10 links on a test doc. If I update
manually{Edit / Links / Update}, then all works well and fast if I have
the excel source open at the time but I do not want to implement it
with a manual step like that.

The strangest part is that if I comment out the following statements,
then it seems to go through each field properly.
.Locked = False
.Update
.Locked = boolLinkState

Please help me. I'm sure it is my programming that is responsible but
can not find the answer. Also, can anyone point me to an online
reference manual for word 2002, excel 2002, or vba?

Pentium 4 w/ 512Mb cache
Operating system XP SP2
Word 2002
Excel 2002

Public Sub UpdateAllLinks()
Dim fldItem As Word.Field
Dim boolFieldLockState As Boolean
Dim boolLinkState As Boolean
fldcount = 0
' All fields in the MainTextStory
For Each fldItem In ActiveDocument.Fields


' Update any linked fields
If fldItem.Type = wdFieldLink Then

boolFieldLockState = fldItem.Locked
If boolFieldLockState Then fldItem.Locked = False

' Now try to update the linked fields
On Error Resume Next
With fldItem.LinkFormat
fldcount = fldcount + 1
If fldcount > 90 Then
Stop
End If

boolLinkState = .Locked
.Locked = False
.Update
.Locked = boolLinkState
End With
On Error GoTo 0
fldItem.Locked = boolFieldLockState
End If
Next
End Sub

 

[macropod]

hi jkbob,

I think you can usefully trim the code to:

Public Sub UpdateAllLinks()
Dim oFld As Word.Field
Dim bState As Boolean
For Each oFld In ActiveDocument.Fields
If oFld.Type = wdFieldLink Then
bState = oFld.Locked
With oFld
.Locked = False
.Update
.Locked = bState
End With
End If
Next
End Sub

Cheers

 

[bob]

It worked. Thank you for your help.