我正在尝试使用变量在 XLS(来自 MSAccess 记录集(中创建一个超链接来引用所需的单元格和值,但我正在努力解决语法问题。
在 MS 站点中,以下内容按预期工作:
With wks1
.Hyperlinks.Add Anchor:=.Range("a5"), _
Address:="https://example.microsoft.com", _
TextToDisplay:="Microsoft"
End With
我可以使用变量和记录集值写入所需的单元格,如下所示:
wks1.Range("B" & r).Value = rs("Name" & n).Value
(where r = XLS cell, i.e., "B4", n = rs member, i.e., "Name5")
当我尝试使用变量方法添加超链接时,它失败了(类型不匹配(:
With wks1
.Hyperlinks.Add Anchor:=.Range("B" & r).value, _
Address:="https://example.microsoft.com", _
TextToDisplay:= rs("Name" & n).Value
End With
完成此任务的任何建议?
谢谢。
定义Range对象并像这样:
Public Function SetLink()
Dim wks1 As Excel.Worksheet
Dim Anchor As Excel.Range
Dim r As Long
r = 9 ' Example.
Set wks1 = ThisWorkbook.Worksheets(1)
Set Anchor = wks1.Cells(r, 1)
With wks1
.Hyperlinks.Add Anchor:=Anchor, _
Address:="https://example.microsoft.com", _
TextToDisplay:="Microsoft"
End With
Set Anchor = Nothing
Set wks1 = Nothing
End Function