正在从外部数据库获取超链接

Question

我在从外部源获取超链接的值或URL时遇到问题。

我有一个单元格，其公式如下：

=HYPERLINK([@[CHANGE_LINK]])

它从外部数据源获取URL并将其放入单元格中。

在我的VBA子中，我有以下内容：

Dim cellRow As Integer
Dim URL As String
Dim rng As Range
Set rng = Sheets(changeSheet).Range("A1:A2000").SpecialCells(xlCellTypeVisible)
cellRow = 4
For Each Value1 In rng.SpecialCells(xlCellTypeVisible)
    URL = Range(Value1.Address).Value '<----CANT GET URL!
    MsgBox URL
    Sheets(sheetname).Range("B" & cellRow & ":B" & cellRow).Value = "=HYPERLINK(" & URL & ",""View Change"")"
    cellRow = cellRow + 1
Next Value1

但是，我的问题是，如何将该公式的URL转换为VBA变量？

我已经尝试了多种方法，但似乎没有一种能得到URL：

Range("A1:A1").value
Range("A1:A1").Address
Range("A1:A1").Formula
Range("A1:A1").Hyperlink(1)
Range("A1:A1").Hyperlink(1).Item(1)
Range("A1:A1").Hyperlinks
Range("A1:A1").Hyperlink.address
Range("A1:A1").Hyperlink.subaddress

更新日期：2015年3月27日

我使用下面的代码，它应该可以工作。然而，它不会将文本检索为字符串。它似乎不允许我在脚本中使用它。。。不知道为什么。。

Dim row As Range
Dim cellRow As Integer
Dim URL As String
Dim rng As Range
Set rng = Sheets(changeSheet).Range("B19:B2000")
cellRow = 4
For Each row In rng.SpecialCells(xlCellTypeVisible)
    URL = row.Text <--Application doesnt like the URL parsed into the statement below:
    Sheets(sheetname).Range("B" & cellRow & ":B" & cellRow).Value = "=HYPERLINK(" & URL & ",""View Change"")" <---This is now the issue, ERROR 1004, Application or Object defined error....
    cellRow = cellRow + 1
Next row

Answer 1

所以我认为你的根本问题是，在函数设置行中，你需要将URL放在双引号中，这样行就变成了：

Sheets(sheetname).Range("B" & cellRow & ":B" & cellRow).Function = "=HYPERLINK(""" & URL & """,""View Change"")"

请注意URL变量周围额外的双引号。Microsoft帮助文档中的示例清楚地显示了HYPERLINK函数中URL需要加引号，以及在没有这些引号的情况下中止单元格的设置。

相对于您标记为[URL=Range（Value1.Address）.Value]未获取URL的行。如果该行确实也有问题，我们需要查看您的源数据。然而，在我的简单调试中，我将超链接添加到源数据单元格中（而不是URL的字符串），并且您上面的代码行可以很好地将URL从单元格中的超链接中取出。

在"For Each"语句中冗余地放置".SpecialCells（xlCellTypeVisible）"似乎没有必要，因为它已经是"rng"变量中的限定符了。没有它对我来说效果很好，而且我确实在调试测试中隐藏了一些源数据行，只是为了确保它们被跳过。

Answer 2

您的链接是由公式创建的
因此，您可以使用Formula Property检索它们
现在，如果你的URL's总是以字符串的形式存在，你可以在下面尝试

Dim r As Range, c As Range
Set r = Sheets("SheetName").Range("A1:A2000").SpecialCell(xlCellTypeFormulas)
For Each c In r
    Msgbox Split(c.Formula, """")(1)
Next

这将返回您所有的URL's
但是，如果它指向一个单元格或外部引用，您可以尝试Evaluate，如下所示：

Dim f As String, myurl As String
For Each c In r
    f = c.Formula
    myurl = Evaluate(Split(Mid(f, InStr(f, "(") + 1), ")")(0))
    MsgBox myurl
Next

这适用于公式：=HYPERLINK($B$1)，其中$B$1包含实际路径
它将返回存储在单元格引用中的实际URL
我无法确定它是否适用于你的案子，但你可以试一试。

附加：

您的公式省略了[friendly_name]参数，它应该显示单元格上的实际路径
如果是，则可以使用.Value属性检索URL
尽管你在帖子中提到了这一点，但你确定这不起作用吗？