Linked Graph/Chart source change fails with vba

Hi, I have been lurking these forums for a while and have found it a great resource, which is why I am hoping someone may be able to help.

I have supplied a bit of info below, please ask if more is needed to help.

What I am trying to Achieve
I have a Word doc with a series of charts linked to a worksheet in an excel workbook. I would like to use vba to update all these links to link to a separate worksheet in the same workbook.

Some background.
The Excel worksheet contains multiple copies of a master sheet, each with 4 charts.
As the sheet was setup before duplication, each sheet contains chart1,chart2..-chart4.
The Sheets are named 'Site 1', 'Site 2' and so on.
I also have some calculations present on these sheets which I also link into word.
Linked charts were pasted into word 2010 via 'Paste Special', 'Paste Link', 'MS Excel Chart Object'
Linked Cells: 'Paste Special', 'Paste Link', 'Unformated Text'

Chart link format in word:
{LINK Excel.SheetMacroEnabled.12 "\\\\server\\Workbook.xlsm" "Site 1![Workbook.xlsm]Site 1 Chart 1" \a \p}

The story so far.
I have spent a lot of time trying to narrow down the cause of the issue.

• When the word doc only contains charts, the update works.
• When a link to a cell is added the macro works until the file is closed and reopened.
  
  
  • When the file is reopened, the macro fails to update the charts on the first run.

  
  

• The macro (code below) works and successfully changes the link text (seen in word using Alt+F9)
• When the macro updates the links, the linked charts revert to the original worksheet.
• If I re run the macro a second time, it then successfully updates the chart links to the new worksheet.
• I have tried duplicating the code in the macro but this does not work, as the source reverts after the macro has finished.

Code - this is the main part, simplified to what is relevant.

Sub AA_LinkUpdate()
Dim thisField As Field
Dim OldLink As String
Dim NewLink As String


OldLink = "Site 1"
NewLink = "Site 2"
        
For Each thisField In ActiveDocument.Fields
    thisField.Code.Text = Replace(thisField.Code, OldLink, NewLink, , , 1)
Next thisField


Selection.WholeStory
Selection.Fields.Update
Selection.Collapse 'deselect text


End Sub

The above will successfully change all text fields on the first pass. I have to rerun it to get it to change the chart fields. Re running is not an option as I need to save to pdf after each change.

Any help would be much appreciated.

Re: Linked Graph/Chart source change fails with vba

I ended up solving the issue by using two passes, the first to change the text links, then the second to change the chart links. I don't like the solution as I don't fully understand why it works. I have a sneaking suspicion it has something to do with word caching field source values, although this is only speculation.
For those who come accross this later, the working code is below.
If you have an improvement or can explain why the problem occurred in the first place, I would be grateful if you could post a response.

[COLOR=Blue]Sub[/COLOR] AALinkUpdateAll()
[COLOR=Blue]Dim[/COLOR] thisField [COLOR=Blue]As[/COLOR] Field
[COLOR=Blue]Dim[/COLOR] OldLink, NewLink [COLOR=Blue]as[/COLOR] [COLOR=Blue]String[/COLOR]
OldLink = InputBox([COLOR=#a31515]"Text To Replace"[/COLOR], , OldLink)
NewLink = InputBox([COLOR=#a31515]"Replace With"[/COLOR], , NewLink)


[COLOR=Blue]For[/COLOR] [COLOR=Blue]Each[/COLOR] thisField [COLOR=Blue]In[/COLOR] ActiveDocument.Fields [COLOR=Green]'change text fields[/COLOR]
  [COLOR=Blue]If[/COLOR] InStr(1, thisField, [COLOR=#a31515]"!R"[/COLOR], vbTextCompare) > 0 [COLOR=Blue]Then[/COLOR] [COLOR=Green]' "!R" is the start of a cell reference in the link field[/COLOR]
    thisField.Code.Text = Replace(thisField.Code, OldLink, NewLink, , , 1)
  [COLOR=Blue]End[/COLOR] [COLOR=Blue]If[/COLOR]
[COLOR=Blue]Next[/COLOR] thisField


[COLOR=Blue]For[/COLOR] [COLOR=Blue]Each[/COLOR] thisField [COLOR=Blue]In[/COLOR] ActiveDocument.Fields [COLOR=Green]'change chart fields[/COLOR]
  [COLOR=Blue]If[/COLOR] InStr(1, thisField, [COLOR=#a31515]"Chart"[/COLOR], vbTextCompare) > 0 [COLOR=Blue]Then[/COLOR] [COLOR=Green]' "Chart" is the start of a chart reference in the link field[/COLOR]
    thisField.Code.Text = Replace(thisField.Code, OldLink, NewLink, , , 1)
  [COLOR=Blue]End[/COLOR] [COLOR=Blue]If[/COLOR]
[COLOR=Blue]Next[/COLOR] thisField


Selection.WholeStory
Selection.Fields.Update
Selection.Collapse


[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]