Changing a link in a workbook programmatically

Question

Everything,

I have the following code to change a workbook link.

ThisWorkbook.ChangeLink "OldPath.xlsx", "NewPath.xlsx", xlLinkTypeExcelLinks

My problem is that "NewPath.xlsx" is a book that has a slightly different name for the WorkSheet object than in OldPath.xlsx. So, is there a way to programmatically change not only the path associated with the Excel workbook, but the actual link to the object / range or external link?

Thanks in advance.

Answer 1

The only idea I have is an extended version of @ d-stroyer solution. You also need to replace part of the formulas, including the path to the document (or just a link to the book, if it is open):

 Cells.Replace What:="C:\Users\Name\Desktop\[OldPath.xlsx]OldSheetName", _ Replacement:="C:\Users\Name\Desktop\[NewPath.xlsx]NewSheetName", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False 

Unfortunately, this will not work (I think it has not been verified) if you want to replace links in other excel elements, such as: a series of diagrams, possibly a data source for graphic elements, etc.

Answer 2

Yes there is. Use a simple Replace statement:

 Sub UpdateSheetName() Cells.Replace What:="OldSheetName", Replacement:="NewSheetName", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub 

If there are other occurrences of the sheet name in your book and you do not want them to be caught in the search, use a limited range. In addition, you can limit your search to "OldSheetName!". (with "!") and replacing "NewSheetName!".