发信人: qq (小捕快), 信区: WindowsTech excel文档 |
假设数据位于A列,文件夹路径为D:\My Documents\
注意路径后面的一个斜杠\不能漏掉。
辅助列函数法:
B1中输入
=HYPERLINK("D:\My Documents\"&A1,A1)
向下填充
vba法不用加辅助列:
Sub Macro1() For i = 1 To 5000 strValue = CStr(Range("A" & i)) ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), _ Address:="D:\My Documents\" & strValue, TextToDisplay:=strValue Next End Sub |
提取超链接内容的自定义函数(转自ExcelHome):
Option Explicit Function GetName(HyCell) Application.Volatile True GetName = HyCell.Hyperlinks(1).Name End Function Function GetAddress(HyCell) Application.Volatile True With HyCell.Hyperlinks(1) GetAddress = IIf(.Address = "", .SubAddress, .Address) End With End Function |
评论