vba 在 Excel 中，如何以编程方式编辑包含超链接的一系列单元格中的地址？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8678752/
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
In Excel, how can I programmatically edit the address in a range of cells containing hyperlinks?
提问by Howiecamp
I have a column containing hyperlinks and I want to do a programmatic search and replace of the hyperlink address - the address itself, not the text to display. How do I do this?
我有一个包含超链接的列，我想进行程序化搜索并替换超链接地址 - 地址本身，而不是要显示的文本。我该怎么做呢？
采纳答案by mtone
By modifying the "Address" property of the Hyperlinks element of a range object. Hyperlinks is a collection, so you will pick the first item if your range is a single cell, but it allows you to easily loop through all the links contained in a given range or worksheet.
通过修改范围对象的超链接元素的“地址”属性。超链接是一个集合，因此如果您的范围是单个单元格，您将选择第一个项目，但它允许您轻松地循环浏览给定范围或工作表中包含的所有链接。
Here's an example to change the link of the currently selected cell only (note: it must already have an hyperlink).
这是一个仅更改当前选定单元格链接的示例（注意：它必须已经有一个超链接）。
Sub ChangeLink()
Application.ActiveCell.Hyperlinks(1).Address = "www.test.com"
End Sub
Here's a variation that changes all links in the current worksheet to www.google.com, as long as they are not currently pointing to www.google.com (not necessary, but just for demonstration's sake):
这是将当前工作表中的所有链接更改为 www.google.com 的变体，只要它们当前未指向 www.google.com（不是必需的，只是为了演示）：
Sub ChangeLinks()
For x = 1 To Application.ActiveSheet.Hyperlinks.Count
If Application.ActiveSheet.Hyperlinks(x).Address <> "www.google.com" Then
Application.ActiveSheet.Hyperlinks(x).Address = "www.google.com"
End If
Next x
End Sub
You can also add and delete links using the Hyperlinks.Add and .Delete methods. The TextToDisplay property allows you to change the text, and the Range property allows you to access (or figure out) which cells a given link belongs to.
您还可以使用 Hyperlinks.Add 和 .Delete 方法添加和删除链接。TextToDisplay 属性允许您更改文本，而 Range 属性允许您访问（或找出）给定链接所属的单元格。
EDIT: As requested, here's how to do it on all links in one specific column.
编辑：根据要求，这里是如何在一个特定列中的所有链接上执行此操作。
Most often I like to create a few variables to assign (Set) the objects I'm working with, such as the worksheet (calling "Sheet1" here), then the actual column which I will refer to by myColumn. If you get an error message, this allows you to pinpoint where it fails exactly.
大多数情况下，我喜欢创建一些变量来分配（设置）我正在使用的对象，例如工作表（此处称为“Sheet1”），然后是我将通过 myColumn 引用的实际列。如果您收到错误消息，这可以让您准确指出它失败的地方。
Notice how the Hyperlinks collection adjusts itself nicely according to the object you call it from!
请注意 Hyperlinks 集合如何根据您调用它的对象很好地调整自身！
I also added a check that shows a message if there are no links in the specified column.
我还添加了一个检查，如果指定列中没有链接，则会显示一条消息。
Sub ChangeLinksInColumn()
Dim mySheet As Worksheet
Dim myColumn As Range
Set mySheet = Application.Worksheets("Sheet1")
Set myColumn = mySheet.Columns(3) ' Enter column number here
' Check if column contains hyperlinks
If myColumn.Hyperlinks.Count = 0 Then
MsgBox "No hyperlinks in specified column."
Else
' Perform task in myColumn only
For x = 1 To myColumn.Hyperlinks.Count
myColumn.Hyperlinks(x).Address = "www.google.com"
Next x
End If
End Sub
