Updating a link with VBA corrupts it

[BillHamilton]

I have some code, below, which updates three links in a Word doc to bring in the values of cells in an Excel workbook sheet. All I want to do is to change the source workbook from one to another and keep everything else the same. I've used the 'replace' method of updating the links rather than the 'LinkFormat.SourceFullName' way, but the same symptom shows up there too.

There are two problems:
1. For the two links (to single cells) that have the \* MERGEFORMAT switch, then after the update that switch is missing.
2. For the link to a range of cells linked as a 'Microsoft Excel Worksheet Object', the reference to the source range gets lost, and after the update the link just brings in the whole of the first sheet in the workbook as the worksheet name has gone too.

The Debug.Print output below the code shows the behaviour.

Set dDoc = Documents.Open(stFileToUpdate)
ActiveWindow.WindowState = wdWindowStateMinimize
On Error GoTo LinkError
With dDoc
  For n% = 1 To .Fields.Count
    With .Fields(n%)
      If .Type = wdFieldLink Then
        stOrigLink = .Code.Text
Debug.Print "Old" & CStr(n%) & ":    " & .Code.Text; ""
        stOldLinkFile = Replace(.LinkFormat.SourceFullName, "\", "\\")
' stNewLinkFile used in the next line is set up earlier as the full pathname to the new file, with doubled path separators.
        stNewFieldCode = Replace(.Code.Text, stOldLinkFile, stNewLinkFile) 
Debug.Print "Update" & CStr(n%) & ": " & stNewFieldCode
        .Code.Text = stNewFieldCode
Debug.Print "New" & CStr(n%) & ":    " & .Code.Text
        .LinkFormat.AutoUpdate = False
      End If
    End With
  Next
  .Fields.Update
End With

Here are the Debug.Prints. The first two are for links to named single cells and the third is to a named range of cells, all in a sheet called Draw. The 'Old' values are the original link, the 'Update' values show a variable containing the value of the original link with the filename replaced with the new one, and the 'New' values are the values of the link after being updated.

Old1: LINK Excel.Sheet.12 "F:\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!season" \t \* MERGEFORMAT
Update1: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!season" \t \* MERGEFORMAT
New1: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!season" \t

Old2: LINK Excel.Sheet.12 "F:\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!date" \t \* MERGEFORMAT
Update2: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!date" \t \* MERGEFORMAT
New2: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "Draw!date" \t

Old3: LINK Excel.Sheet.12 "F:\\WorldCup\\Final\\Draw_Details.xlsx!Draw!allgroups" "" \p
Update3: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "" \p
New3: LINK Excel.Sheet.12 "E:\\Documents\\WorldCup\\Final\\Draw_Details.xlsx" "" \p

As you can see, the 'New1' and 'New2' values have lost the \* MERGEFORMAT switch. Is there any way I can just add that back in?
For the third link, the sheet and range names given after the filename (Draw!allgroups) has somehow been lost in the statement
stNewFieldCode = Replace(.Code.Text, stOldLinkFile, stNewLinkFile)
I have no idea how to fix that.

I note the different formats of the two types of link text, and I'm guessing the third one is the format for a Worksheet Object (unless it's the \p switch - no idea, nor what the \t switch does either).

Has anyone got a clue about what's happening and how to prevent it?

I'm using Windows 10 and Office 2016.

(I also occasionally get messages about Excel not being able to open two workbooks with the same name, but that's probably a topic for another thread.)

Bill

[macropod]

Your code is overly complicated and slow. Try:

Dim wdFld As Word.Field
Set dDoc = Documents.Open(stFileToUpdate)
ActiveWindow.WindowState = wdWindowStateMinimize
With dDoc
  For Each wdFld In .Fields
    With wdFld
      If .Type = wdFieldLink Then
        With .LinkFormat
          .SourceFullName = .SourcePath & "\" & stNewLinkFile
          .AutoUpdate = False
        End With
      End If
    End With
  Next
  .Fields.Update
End With

[BillHamilton]

Thanks for the reply, but I feel I'm going backwards.

I realise my code is a bit clunky, but the reason I was using the 'Replace' method on Code.Text was that I originally did as you are suggesting and just changed the .SourceFullName value in the link. However, I always got the messages I mentioned at the end of my post about Excel not being able to open two workbooks with the same name at the same time (given twice in quick succession before producing error 6083 - "Objects in this document contain links to files that cannot be found"). I then discovered the 'Replace' method, which did not give those errors but removed the MERGEFORMAT switch. That's when I gave up and asked for help here.

I have re-tried your code and it still gives the Excel/two files error. (Incidentally, only the Path is changing; the filename remains the same. Maybe my variable names were not clear, but I got the idea of your suggestion).

Incidentally I was tearing my hair out for two days with the disappearing MERGEFORMAT switch before I posted here about that. I did the post, went away for a few hours and just re-tried it. The MERGEFORMAT switches magically decided to return after the the link was updated. No changes to anything in the meantime and the files had not even been closed. Baffling. The resulting links didn't work properly, though. The data values were brought in OK but their formatting was different from both the source Excel cells and the target Word file. The font sizes were different - 10 instead of 14 and 12 respectively. On manually editing the new links, the 'Preserve formatting after update' checkbox is Off (I thought the MERGEFORMAT switch was supposed to turn that on). If I turn it on, reset the formatting in the doc to what it should be and update the link manually, the formatting remains correct.

I'm pretty-much stumped now so any advice would be appreciated. I have about 30 docs to update with revised links to files (one Word file, the rest Excel) in different paths and one of them has about 80 links. I have no plans to do any of that manually so I need to get this working and I'm nowhere near getting the source change to the Worksheet Object link to work.

[macropod]

​You should be able to resolve that by turning off the 'update automatic links at open' option (File|Options|Advanced>General).

[BillHamilton]

Thanks.

There are no automatic links involved in any of this but I switched that option off as suggested and it made no difference to either method. Still get the Excel/two files error or the links don't work properly with formatting or Worksheet Objects.