excel - 变量中的工作表名称包括文件路径

Question

我将工作表名称存储在变量中。当我使用该变量创建超链接时，生成的链接包含我不想要的文件路径，我只想要工作表名称。非常感谢任何帮助。

Dim wsname as string

wsname = ActiveSheet.Name  ' at this point wsname = "sheetname"

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=wsname, SubAddress:= _
        "", TextToDisplay:="Tasks" 

链接创建为"C:\\.....\filename\\"sheetname"

我也尝试将 wsname 声明为 Worksheet（Dim wsname As Worksheet）。

标签： excelvbahyperlink

Answer 1

超链接到（一个单元格）另一个工作表

• 第一个示例在链接到同一工作表中的单元格的Selection（如果它是一个范围）的第一个单元格中创建一个超链接。A1
• 第二个示例在Selection（如果它是一个范围）的第一个单元格中创建一个超链接，该超链接链接到同一工作簿 ( ) 中的A1单元格。Sheet2ActiveWorkbook

Option Explicit

Sub addHyperlink()
        
    Const dAddress As String = "A1"
        
    If TypeName(Selection) = "Range" Then
        
        Dim sws As Worksheet: Set sws = ActiveSheet
        Dim sCell As Range: Set sCell = Selection.Cells(1)
   
        sws.Hyperlinks.Add _
            Anchor:=sCell, _
            Address:="", _
            SubAddress:="'" & sws.Name & "'!" & dAddress, _
            TextToDisplay:="Tasks"
    
    End If

End Sub

Sub addHyperlink2()
        
    Const dName As String = "Sheet2"
    Const dAddress As String = "A1"
        
    If TypeName(Selection) = "Range" Then
        
        Dim sws As Worksheet: Set sws = ActiveSheet
        Dim sCell As Range: Set sCell = Selection.Cells(1)
        
        On Error Resume Next
        Dim dws As Worksheet: Set dws = sws.Parent.Worksheets(dName)
        On Error GoTo 0
        
        If Not dws Is Nothing Then
            sws.Hyperlinks.Add _
                Anchor:=sCell, _
                Address:="", _
                SubAddress:="'" & dws.Name & "'!" & dAddress, _
                TextToDisplay:="Tasks"
        Else
            MsgBox "The worksheet '" & dName & "' does not exist.", _
                vbCritical, "Add Hyperlink"
        End If
    
    Else
    
        MsgBox "The current selection is not a range.", _
            vbCritical, "Add Hyperlink"
    
    End If

End Sub