Excel 列出图片上的内部超链接

Excel 列出图片上的内部超链接

我有此代码来列出带有超链接的图片/文本框,但它只列出外部超链接,有没有办法将其更改为也包含内部链接?

    For Each wsSheet In wbBook.Worksheets
    
        wsSheet.Activate
        
        For Each Hint In ActiveSheet.Shapes
        On Error Resume Next
        
            Set hl = Hint.Hyperlink
            On Error GoTo 0
            If Not hl Is Nothing Then
               
                Print #intFile, hl.Address
                Set hl = Nothing

            End If
       
        Next

    Next wsSheet

答案1

您必须检查超链接AddressSubAddress。网站使用Address和内部链接使用SubAddress

这是一个小子程序,它循环遍历单个工作表上的所有形状,并确定是否Shape有链接以及它的类型:

Sub jhahgdsdsf()
    Dim s As Shape, hy As Hyperlink
    Dim msg As String
    
    For Each s In ActiveSheet.Shapes
        On Error Resume Next
            Set hy = s.Hyperlink
            x = Err.Number
        On Error GoTo 0
        
        If x <> 0 Then
            msg = msg & vbCrLf & s.Name & " " & "has no hyperlink"
        Else
            If hy.Address <> "" Then
                msg = msg & vbCrLf & s.Name & " " & "has hyperlink with address " & hy.Address
            Else
                msg = msg & vbCrLf & s.Name & " " & "has hyperlink with sub-address " & hy.SubAddress
            End If
        End If
    Next s
        
    MsgBox msg
End Sub

在此处输入图片描述

相关内容