ChangeLinks fails if formulae exceed 1024 character limit

[Paul Martin]

Hi all

I have written some code that loops through the links in a workbook and
updates them to different paths. To be more specific, it updates the
paths from drive letters to UNC paths.

Generally, this works, but fails where the new path forces formulae in
the workbook to exceed Excel's 1024 character limit. Interestingly,
the link update fails, but it raises no error, so there's not an
automatic means of determining that one or more links has failed to
update.

I would like to know after the code has completed whether any updates
have failed. Does anyone have a fix or workaround for this?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Tom Ogilvy]

I haven't run into it, so I don't have a tested workaround, but here is a
thought:

did the LinkSources list change? if not, you could get the list again
(after the change) and compare to the original list - if they haven't
changed, you know you have a problem. Or compare the new list to see it
the desired change is included.

 

[Paul Martin]

Hi Tom

Yes, that sounds like a good solution. I'll give it a try.

Thanks

Paul

 

[Paul Martin]

For the benefit of anyone interested, the solution I used was:

astrTestLinks = Workbooks(intWbk).LinkSources(xlExcelLinks)
blnLinkUpdated = True

For i = 1 To UBound(astrLinks)
If astrTestLinks(i) = strLink Then
blnLinkUpdated = False
Exit For
End If
Next i

If blnLinkUpdated = True Then
' Success
Else
' Failure: updating link causes formulae to exceed XL's 1024
character limit
End If

An interesting observation during debugging was that the links
collection is random in order so there was the need to loop through all
links to see if the link that had been apparently updated still
existed.

Paul