替换超链接的一部分 [英] Replace part of hyperlink

问题描述

我需要修改.xls工作簿中的许多超链接.我的链接是这样的:

I need to modify a lot of hyperlinks in an .xls workbook. My links are like this:

\\\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:我正在使用Office 2000

PS: I'm using Office 2000

推荐答案

糟糕！您正在提取并保留路径字符串的左侧部分，您真正想要做的是丢弃！

Whoops! You're extracting and keeping the left part of your path string, where what you really want to do is to discard it!

此外，您不能在这样的超链接对象上使用字符串函数(Left，Right，Len ...).这是导致错误的原因.您必须提取超链接对象的Address属性-这是一个字符串.

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.

替换

path = Left(hLink, 11) ' throws error: Object doesn't support this property...

与

path = Mid(hLink.Address, 12) ' returns "some\path\documents.doc"
' or, equivalently:
'path = Right(hLink.Address, Len(hLink.Address) - 11) 

这篇关于替换超链接的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！