在Excel中使用VBA打开超链接（运行时错误9） [英] Open Hyperlinks Using VBA in Excel (Runtime Error 9)

问题描述

我正在尝试使用VBA从我的excel中使用以下代码打开超链接：

I am trying to use VBA to open hyperlinks from my excel using the following code:

numRow = 1
Do While WorksheetFunction.IsText(Range("E" & numRow))
    ActiveSheet.Range("E" & numRow).Hyperlinks(1).Follow
    numRow = numRow + 1
Loop

但是，我不断得到运行时错误9：范围在我遵循超链接的代码中。

However, I keep getting Runtime Error 9: Subscript out of range at the point in the code where I follow the hyperlinks.

我对VBA宏观制作非常新鲜（因为在'之前完成'），所以帮助不胜感激。 （如果有一个更好的方式来打开一个列中每个单元格的链接，我也很乐意了解这一点）

I'm pretty new to VBA Macro-making (as in-'never done it before'), so help would be appreciated. (And if there's a better way to open a link from each cell in a single column, I'd appreciate learning about that too)

编辑（添加更多信息）

EDIT (To add more Info)

使用HYPERLINK Worksheet功能创建了有关的超链接，文本不显示链接URL。工作表数据示例是这样的：

The hyperlink in question has been created using HYPERLINK Worksheet function and the text does not display the link URL. Sample of worksheet data is something like this:

看起来像

案例 ------ 链接

Case1 -----总结

Case2 -----总结

Case3 -----总结

Case ------ Link
Case1----- Summary
Case2----- Summary
Case3----- Summary

然而，显示文本摘要的单元格包含公式

The Cells showing the text "Summary", however, contain a formula

=HYPERLINK("whateverthebaseurlis/"&[@[Case]]&"/Summary", "Summary")

这是必须遵循的链接。链接工作，可以手动进行。但是我需要通过宏来实现

And this is the link that has to be followed. The link works, it can be followed manually. But I need to do it via macro

谢谢

推荐答案

它正在抛出您尝试打开超链接的错误，尝试使用explorer.exe显式打开它

If it is throwing the error where you try to open hyperlinks, try and explictly open it using explorer.exe

Shell "explorer.exe " & Range("E" & numRow).Text

原因超链接1）.Follow 不工作，这不是单元格中常规的超链接，所以它将返回超出范围

the reason Hyperlinks(1).Follow not working is that is no conventional hyperlink in the cell so it will return out of range

numRow = 1
Do While WorksheetFunction.IsText(Range("E" & numRow))
    URL = Range("E" & numRow).Text
    Shell "C:\Program Files\Internet Explorer\iexplore.exe " & URL, vbNormalNoFocus
    numRow = numRow + 1
Loop

一个类似的问题：
http://www.mrexcel.com/forum/excel-questions/381291-activating-hyperlinks-via-visual-basic-applications.html

这篇关于在Excel中使用VBA打开超链接（运行时错误9）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！