[求助]一个困扰我很久的外部链接问题

dengruif

一个模板文件切换三个以上同一格式的外部链接时vba该怎么写？例如：当前文件要切换a.xls、b.xls、c.xls、d.xls四个链接时,当前链接为a,b,c任何一个时,我要直接切换到d,现在我折衷做法是设六个子过程,但切换起来很烦,还要清楚当前链接,

附件请解压到D:\ljwt\下运行

Sub a_b() ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\b.xls", Type:=xlExcelLinks End Sub Sub a_c() ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\c.xls", Type:=xlExcelLinks End Sub Sub a_d() ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks End Sub Sub b_a() ActiveWorkbook.ChangeLink Name:="D:\ljwt\b.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks End Sub Sub c_a() ActiveWorkbook.ChangeLink Name:="D:\ljwt\c.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks End Sub Sub d_a() ActiveWorkbook.ChangeLink Name:="D:\ljwt\d.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks End Sub

Mh8BHg05.rar (21.2 KB, 下载次数: 8)

2004-5-19 11:42 上传

点击文件名下载附件
[求助]一个困扰我很久的外部链接问题

[此贴子已经被作者于2004-5-19 11:16:57编辑过]

cxffxc

不知道你的意思，直接用超级链接不可以吗？

dengruif

cxffxc 你好，

我的意思是“模板.xls”文件a1单元格有其他文件(a,b,c,d中的任意一个)外部链接，当我不知当前链接文件时，如何一步切换到我想要链接的文件来更新数据，而不是打开那个文件！

附件请解压到D:\ljwt\下运行

0XhnvEe6.rar (21.2 KB, 下载次数: 6)

2004-5-19 11:44 上传

点击文件名下载附件

[此贴子已经被作者于2004-5-19 11:16:57编辑过]

dengruif

问题已经解决！在MS VB帮助中找到答案，即在可能发生错误之前加入On Error Resume Next语句！

RIyJ68ME.rar (22.52 KB, 下载次数: 8)

2004-5-20 07:25 上传

点击文件名下载附件

请解压在D:\ljwt\下运行

Sub aa() On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\b.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\c.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\d.xls", NewName:="D:\ljwt\a.xls", Type:=xlExcelLinks End Sub

Sub bb() On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\b.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\b.xls", NewName:="D:\ljwt\b.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\c.xls", NewName:="D:\ljwt\b.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\d.xls", NewName:="D:\ljwt\b.xls", Type:=xlExcelLinks End Sub Sub cc() On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\c.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\b.xls", NewName:="D:\ljwt\c.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\c.xls", NewName:="D:\ljwt\c.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\d.xls", NewName:="D:\ljwt\c.xls", Type:=xlExcelLinks End Sub Sub dd() On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\a.xls", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\b.xls", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\c.xls", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks On Error Resume Next ActiveWorkbook.ChangeLink Name:="D:\ljwt\d.xls", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks End Sub

Resume Next:如果错误和错误处理程序出现在同一个程序中，则从紧随产生错误的语句的下个语句恢复运行。如果错误发生在被调用的过程中，则对最后一次调用包含错误处理程序的过程的语句（或 On Error Resume Next 语句），从紧随该语句之后的语句处恢复运行。

但还不是很理想，代码太长，能不能在不知当前链接（包括范围）的情况下切换链接,假设选提取当前链接"X"

ActiveWorkbook.ChangeLink Name:="X", NewName:="D:\ljwt\d.xls", Type:=xlExcelLinks

请高手解答！

[此贴子已经被作者于2004-5-20 7:25:37编辑过]