如何使用Excel宏提取单个单元格内的全部超链接及对应显示文本

核心问题说明 #

原代码仅读取单元格超链接集合的第1个元素，且默认一个单元格仅存在一条超链接，因此无法覆盖单单元格多超链接的场景。

主要修改点 #

• 移除原单地址提取函数，新增单元格超链接集合遍历逻辑
• 调整显示文本获取逻辑，读取单条超链接自身的显示文本而非整个单元格文本
• 兼容全版本Excel的行数上限，替换硬编码行号限制

修改后完整代码 #

Option Explicit
Sub DistillHyperlinks()
    Dim cl As Range, wsTarget As Worksheet, clSource As Range
    Dim hl As Hyperlink, nextRow As Long
    
    Application.ScreenUpdating = False
    Set clSource = Selection
    
    ' 检测或创建结果工作表
    On Error Resume Next
    Set wsTarget = Sheets("Hyperlink List")
    If Err.Number <> 0 Then
        Set wsTarget = Worksheets.Add
        With wsTarget
            .Name = "Hyperlink List"
            With .Range("A1")
                .Value = "Location"
                .ColumnWidth = 20
                .Font.Bold = True
            End With
            With .Range("B1")
                .Value = "Displayed Text"
                .ColumnWidth = 25
                .Font.Bold = True
            End With
            With .Range("C1")
                .Value = "Hyperlink Target"
                .ColumnWidth = 40
                .Font.Bold = True
            End With
        End With
        Set wsTarget = Sheets("Hyperlink List")
    End If
    On Error GoTo 0
    
    ' 遍历选中区域每个单元格
    For Each cl In clSource
        ' 遍历当前单元格所有超链接
        For Each hl In cl.Hyperlinks
            nextRow = wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row + 1
            ' 写入原单元格位置跳转链接
            wsTarget.Hyperlinks.Add Anchor:=wsTarget.Cells(nextRow, "A"), _
                                   Address:="", _
                                   SubAddress:=cl.Parent.Name & "!" & cl.Address
            ' 写入当前超链接显示文本
            wsTarget.Cells(nextRow, "B").Value = hl.TextToDisplay
            ' 写入当前超链接目标地址
            wsTarget.Hyperlinks.Add Anchor:=wsTarget.Cells(nextRow, "C"), Address:=hl.Address
        Next hl
    Next cl
    
    wsTarget.Select
    Application.ScreenUpdating = True
End Sub

使用说明 #

1. 打开目标Excel文件，按Alt+F11打开VBA编辑器，将上述代码替换原有宏代码
2. 返回Excel界面，选中需要提取超链接的单元格区域
3. 运行DistillHyperlinks宏，所有超链接（含单单元格内的多条超链接）都会被提取到自动生成的「Hyperlink List」工作表中

内容的提问来源于stack exchange，提问作者user57358