超链接到新的工作簿

Question

我是Excel的新手，所以我希望这有意义。下面的代码显示了单击用户表格上的按钮时，在特定工作簿上创建了一个新表（与当前的工作簿分开）。不过，我在单独的工作簿上创建的表格上的超链接似乎已被打破。我究竟做错了什么？一切都会有帮助，谢谢！

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Employee Information")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
If Me.cbStores.Value = "Northern" Then
Dim newWB As Workbook
Dim thisWB As Workbook
Set thisWB = ThisWorkbook
Set newWB = GetOrCreateWB("EmployeeTemplates", "C:Users...Folder") '<--| Opening EmployeeTemplates wb
thisWB.Sheets("Template").Copy after:=newWB.Sheets(1)
With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + AddEmployeeUF.txtMiddleinitial.Text + AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:="", SubAddress:=.Name & "!A1", TextToDisplay:="View" '<--| hyperlink to new sheet
End With
End If

Answer 1

答案01

此答案使用文件路径，例如" C： Users Me Me Desktop newfile.xlsx"

With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + _
            AddEmployeeUF.txtMiddleinitial.Text + _
            AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it
    ' the hyperlink SubAddress needs a valid file path or hyperlink to
    ' work like "C:UsermeDesktopnewfile.xlsx" 
    ' .Name & "!A1" references a cell not the file location on the computer 
    ' or network
    'ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
    '                  Address:="", SubAddress:=.Name & "!A1", _
    '                  TextToDisplay:="View" '<--| hyperlink to new sheet
    ' you need something like this
    ' as long as newWB.Path property is set you should be good
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
                      Address:="", SubAddress:=newWB.Path, _
                      TextToDisplay:="View" '<--| hyperlink to new sheet
End With

答案02

此答案使用您最初想要的参考。我很难找到指向堆栈溢出问题的链接，但是我测试了代码并有效。我喜欢此选项，但是只要工作簿在同一Excel应用程序中，它才能起作用。如果您打开了两个应用程序，则该应用程序将无效，因为该应用程序中没有提及新工作簿。

With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + _
            AddEmployeeUF.txtMiddleinitial.Text + _
            AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it
    ' the hyperlink SubAddress needs a valid file path or hyperlink to
    ' work like "C:UsermeDesktopnewfile.xlsx" 
    ' .Name & "!A1" references a cell not the file location on the computer 
    ' or network
    'ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
    '                  Address:="", SubAddress:=.Name & "!A1", _
    '                  TextToDisplay:="View" '<--| hyperlink to new sheet
    ' you need something like this
    ' as long as newWB.Path property is set you should be good
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
                      Address:="", SubAddress:="'" & .Name & "'!A1", _
                      TextToDisplay:="View" '<--| hyperlink to new sheet
End With

Answer 2

有人帮助我找到了一个适当的解决方案，因此是：

ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:=newWB.Path & "" & newWB.Name, SubAddress:="'" & .Name & "'!A1", TextToDisplay:="View" '<--| hyperlink to new sheet