How do I write a Macro in Word to update then break Excel links

[IanCAUS]

Is anyone able to help with a macro in Word that's taken up way to much time.

I have a word doc that has multiple links to a excel file. I then want to save the word doc as a template. When the template is opened I want it to update the links then break them. I've set the links to manual update so that the user isn't asked to save twice when they use the template.

I can get the links to update using the first 2 lines of code but if I then try to update and break the links by adding the 3rd line, the update fails?

Any suggestions?

Thanks,
Ian


Private Sub Document_New()

MsgBox ("Process may take a few seconds.")
Selection.WholeStory
Selection.Fields.Update
Selection.Fields.Unlink
Selection.HomeKey

End Sub

 

[jibbajabba]

Is anyone able to help with a macro in Word that's taken up way to much time.

I have a word doc that has multiple links to a excel file. I then want to save the word doc as a template. When the template is opened I want it to update the links then break them. I've set the links to manual update so that the user isn't asked to save twice when they use the template.

I can get the links to update using the first 2 lines of code but if I then try to update and break the links by adding the 3rd line, the update fails?

Any suggestions?

Thanks,
Ian


Private Sub Document_New()

MsgBox ("Process may take a few seconds.")
Selection.WholeStory
Selection.Fields.Update
Selection.Fields.Unlink
Selection.HomeKey

End Sub

Could it be a timing issue? they unlink before they update?

Try putting a msgbox after the update line "fields updated" or getting the return value of the update line

with selection.fields
  if .updatelinks = 0 then
   .unlink
  end if
end with

 

[IanCAUS]

I agree, it seems like timing but I've made a couple of attempts to pause without success.

I tried the changes as suggested (could you check I've written it as you expected, I'm a novice at this stuff) but unfortunatly same result. If I convert the second half of the code to 'text (From with selection.Fields) the links are updated.

Any other thoughts?

Thanks,
Ian

Private Sub Document_New()

MsgBox ("Process may take a few seconds.")

Selection.WholeStory
Selection.Fields.Update
MsgBox ("Break Links now")
With Selection.Fields
If Selection.Fields.Update = 0 Then
.Unlink
End If
End With
Selection.HomeKey

End Sub

 

[jibbajabba]

I agree, it seems like timing but I've made a couple of attempts to pause without success.

I tried the changes as suggested (could you check I've written it as you expected, I'm a novice at this stuff) but unfortunatly same result. If I convert the second half of the code to 'text (From with selection.Fields) the links are updated.

Any other thoughts?

Thanks,
Ian

Private Sub Document_New()

MsgBox ("Process may take a few seconds.")

Selection.WholeStory
Selection.Fields.Update
MsgBox ("Break Links now")
With Selection.Fields
If Selection.Fields.Update = 0 Then
.Unlink
End If
End With
Selection.HomeKey

End Sub

You have two calls to update the fields...

Selection.WholeStory
Selection.Fields.Update - Here!
MsgBox ("Break Links now")
With Selection.Fields
  If Selection.Fields.Update = 0 Then - Here!
   .Unlink
  End If
End With
Selection.HomeKey

You could try this...

Private Sub Document_New()
Dim fielditem As Field

For Each fielditem In ActiveDocument.Fields
fielditem.Update
Next

MsgBox "the fields have been updated"

For Each fielditem In ActiveDocument.Fields
fielditem.Unlink
Next
MsgBox "the fields have been unlinked"
End Sub

you do know how to set a breakpoint in code to step through it?

 

[IanCAUS]

I tried that but again the same deal. If I comment out the second part of the code it works though. Very frustrating.

I don't know how to set a breakpoint? Happy to try anything at this stage.

Thanks,
Ian

 

[jibbajabba]

Where you write the code, there is a grey margin to the left, click on it next to any line of code you want to pause the code at.