VBA检索超链接目标表?

问题描述

我正在尝试从单元格中的超链接中检索工作表引用位置

I'm trying to retrieve the sheet reference location from a hyperlink that's in a cell

以下内容似乎不起作用，因为test不返回任何内容，即使G8指向工作表: KO 在单元格A19

The following doesn't seem to work as test doesn't return anything, even though G8 points to Sheet: KO in Cell A19

test = Range("G8").Hyperlinks(3).Address

现在，如果我运行以下命令:

Now if I run the following:

For Each hl In Sheets("LIST").Hyperlinks
    MsgBox "Range " & hl.Range.Address & " addr " & _
            hl.Address & " text " & hl.TextToDisplay
Next hl

它循环遍历并找到正确的地址，但是我似乎无法弄清楚如何检测到它指向的工作表.循环也有点混乱，因为一旦找到这种情况下的第一个也是唯一的超链接，它就会出错. G8并不总是特定的.我想我可以在其中插入if语句，然后尽早退出for循环.

It cycles through and finds the correct address but I can't seem to work out how to detect the sheet it's pointing. Also the loop is a bit of a mess because it errors out once it has found the first and only hyperlink in this situation. And it's not always specific for G8. I guess I could just throw an if statement in and exit the for loop early.

无论如何，我在stackoverflow中找不到任何地方，谷歌，微软的超链接"文档都提供了一种获取工作表名称的方法.

Regardless of that, I can't find anywhere in stackoverflow, google, microsofts "hyperlinks" docs a way to get the sheet name.

请参见以下示例图示:

推荐答案

SubAddress是您想要的:

Sub Test()

    Dim hl As Hyperlink, r As Range

    Set hl = ActiveSheet.Hyperlinks(1)

    Set r = Application.Evaluate(hl.SubAddress)

    Debug.Print "Sheet: '" & r.Parent.Name & "'", "Range:" & r.Address()

End Sub

这篇关于VBA检索超链接目标表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！