以编程方式更改工作簿中的链接 [英] Change Link in Workbook programmatically
问题描述
全部
我有以下代码来更改工作簿的链接。
I have the following code to change a workbook's link.
ThisWorkbook.ChangeLinkOldPath.xlsx,NewPath.xlsx,xlLinkTypeExcelLinks
我的问题是NewPath.xlsx表示一个工作簿在WorkSheet对象上的名称与OldPath.xlsx中的名称稍有不同。那么,有没有办法以编程方式更改不仅仅是与Excel工作簿关联的路径,还有实际的对象/范围引用或有关的外部链接?
My problem is that "NewPath.xlsx" represents a workbook that has a slightly different name for the WorkSheet Object than in OldPath.xlsx. So, is there a way to programmatically change not just the Path associated with the Excel workbook, but also the actual object/range reference or the external link in question?
谢谢
推荐答案
我唯一的想法是@ d-stroyer解决方案的扩展版本。您需要更换部分公式,包括文档的路径(或简单的工作簿参考,如果打开):
The only idea I have is a extended version of @d-stroyer solution. You need to replace part of the formulas including path to the document as well (or simply workbook reference if one 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
不幸的是,如果要替换其他excel元素中的链接,这将无法正常工作(我认为未测试),如:系列图表,可能的数据来源等等。
Unfortunately, this will not work (I think, not tested) if you want to replace links in other excel elements like: series of charts, possibly data source of pivottables, etc.
这篇关于以编程方式更改工作簿中的链接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!