Updating external links fails if existing link doesn't exist

[BillHamilton]

I want to update existing links in a Word doc that refer to cells in an Excel workbook. I have cribbed some code from the internet which seems to be the standard way of doing this, and the sample I copied works perfectly.

The problem I get is that if the existing links refer to a file in a path that doesn't exist then the code falls over with runtime error 6083 - "Objects in this document contain links to files that cannot be found".

Well duh! That's what I'm trying to fix with this macro - the links are out of date for the latest file structure. I haven't found any way of avoiding this error. Here's the code snippet that does the work after working out all the file and path names:

stNew = (the full path & filename of the new file to replace the old one in the link)
Set dDoc = Documents.Open(full path & filename of the Doc that's to have its links updated)
With dDoc
  For n% = 1 To .Fields.Count
    With .Fields(n%)
      If .Type = 56 Then
        .LinkFormat.SourceFullName = stNew    <== 6083 error occcurs here
        .Update
        .LinkFormat.AutoUpdate = False
        DoEvents
      End If
    End With
  Next n%
End With
dDoc.Close SaveChanges:=wdSaveChanges

If I bracket the failing line (see above) with an On Error Resume Next/GoTo 0 then the .Update on the next line appears to work but the code falls over again on the .LinkFormat.AutoUpdate = False line with Runtime Error 4189 - "Command failed". When the code is stopped at that point I looked at the first link in the Doc file and the .Update had not done anything.

The whole point of this exercise is to replace the out of date links with correct ones. Does some kind person out there know if there any way of getting round the obvious message telling me what I already know and preventing the updates?

(Secondary question - is there any way of updating links like this without having to open the Doc file first? I'm OK with Excel VBA but Word VBA is a bit strange to me sometimes. What's the (simple, I presume) syntax for the Set statement to connect the oDoc object to the file? I tried

Set oDoc=Documents(full pathname)

but it gave Error 4160 - "Bad file name". But it's not, honest, it's a kosher path & file name in a variable that works when used in the Open statement above.)

[macropod]

You should check the LinkFormat state. For example:

If Not .LinkFormat Is Nothing Then
  .LinkFormat.SourceFullName = stNew
  .Update
  .LinkFormat.AutoUpdate = False
End If

[BillHamilton]

Hi,

Thanks for the suggestion, but it gave the same error in the same place. I have managed to solve the problem though: I was looking in the wrong place (as usual).

The error message was not telling me the original link path was wrong (which I knew) but that the new one was wrong, but not obviously wrong. The message is a bit misleading as it does imply exactly that the existing one is wrong as it comes out before anything is changed.

Anyway, the value in stNew was wrong because it originates from a Word table in the document running this macro (from a command button). I was 'Debug.Print'ing every value I could find relating to this process and found that the value shown for stNew was always followed by a blank line with a small vertical oblong character at the left. I googled that and found that that character is the 'end of cell marker' from the table, and it should be stripped off before use.

I did that and hey presto everything worked.

That case is closed but I'd still like to know if it's possible to make these updates to a Doc that is not open (as one can with an Excel file), and if so, what the syntax is for setting an object variable to it. I have tried 'Set odoc=Documents(filepathname)' but that fails.

Just asking. My Word VBA knowledge is sketchy at best so if anyone tells me it's impossible then I'm happy to accept it.

Bill

[macropod]

Unless you want to work at the XML level (which still requires opening the file even though Word isn't used), you can't make the updates without automating Word and having it open the document. Of course, if the only issue is that you don't want to see the document, you could add the Visible:=False argument to 'Documents.Open'.

[BillHamilton]

Hi,

Thanks. You lost me at XML.....

So, I'll take that as a 'no' and close this as Solved.

Bill