vba 替换部分超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7228195/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace part of hyperlink
提问by Remus Rigo
I need to modify a lot of hyperlinks in an .xls workbook. My links are like this:
我需要修改 .xls 工作簿中的大量超链接。我的链接是这样的：
\\\mysrv001\some\path\documents.docand I need to replace \\\mysrv001with \\\mysrv002
\\\mysrv001\some\path\documents.doc我需要替换\\\mysrv001为\\\mysrv002
I tried something like this, but I get an error: "Object doesn't support this property or method". How do I fix this?
我尝试过类似的操作，但出现错误：“对象不支持此属性或方法”。我该如何解决？
Sub test()
Dim hLink As Hyperlink
Dim wSheet As Worksheet
Dim path As String
For Each wSheet In Worksheets
For Each hLink In wSheet.Hyperlinks
path = Right(hLink, Len(hLink) - 11)
hLink.Address = "\mysrv003\" & path
Next hLink
Next
End Sub
PS: I'm using Office 2000
PS：我使用的是 Office 2000
回答by chris neilsen
try this
尝试这个
Sub test()
Dim hLink As Hyperlink
Dim wSheet As Worksheet
For Each wSheet In Worksheets
For Each hLink In wSheet.Hyperlinks
hLink.Address = Replace(hLink.Address, "\mysrv001\", "\mysrv002\")
Next hLink
Next
End Sub
回答by Jean-Fran?ois Corbett
Whoops! You're extracting and keepingthe left part of your path string, where what you really want to do is to discardit!
哎呀！您正在提取并保留路径字符串的左侧部分，您真正想要做的是丢弃它！
EDIT: Also, you can't use string functions (Left, Right, Len...) on a Hyperlink object like that. This is what is causing the error. You have to extract the Hyperlink object's Address property -- that's a string.
编辑：此外，您不能在像这样的超链接对象上使用字符串函数（Left, Right, Len...）。这就是导致错误的原因。您必须提取 Hyperlink 对象的 Address 属性——这是一个字符串。
Replace
代替
path = Left(hLink, 11) ' throws error: Object doesn't support this property...
with
和
path = Mid(hLink.Address, 12) ' returns "some\path\documents.doc"
' or, equivalently:
'path = Right(hLink.Address, Len(hLink.Address) - 11)
