Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data=>EditLinks=>ChangeSource only changes some links

Status
Not open for further replies.

Gavona

Technical User
Aug 27, 2002
1,771
GB
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
 
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
 
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
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top