Data=>EditLinks=>ChangeSource only changes some links

[Gavona]

I have a single linked workbook.
When I use Data=>EditLinks=>ChangeSource only some of the links in my workbook change.
Data=>EditLinks then shows two sourcefiles

I can use Edit Replace to successfully change the links.

I have posted here because the problem occurs in Excel. However this is one step of a process that I am automating in VBA so if there are vba workarounds they would be great - (we can move to the vba forum if the answer).

Other information: The linked workbook(s) were all password protected but I have temporarily removed the passwords as part of my problem solving. I have also tried having the new and the old sourcefiles open before I try changing the source.
I don't see any error messages.

Gavin

 

[Salut39]

HI Gavin,

Check name ranges, occasionally I came across links are transferred with Names. CTRL+F3 and delete or edit names to current workbook.

Yuri

 

[Gavona]

Not that I am afraid. I can readily find the formulae where the links have not updated. I have now written some VBA to work around the issue but it takes a while to run

ThisWorkbook.ChangeLink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

'above changes some but not all the formulae with links so I then loop through the sheets where links exist and use this:

sh.UsedRange.Replace What:=OldLink, Replacement:=NewLink, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

The formulae that do not change all refer to a table in the linked workbook. I reckon that is the issue. Shame I get no error message.
=IFERROR(INDEX(Test.xlsb!InputTable[#All],InputTable[@LastRepRow],AX$3),0)

Gavin