lxy950318
2018-04-12 07:20
采纳率: 100%
浏览 1.9k
已采纳

Excel VBA 超链接中如何在访问地址时并选中指定文件的名字

求解Excel VBA 超链接中如何在访问地址时并选中指定文件夹

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • weixin_41961050 2018-04-12 07:34
    已采纳

    代码实现重新构建所有链接,如果要单独刷新某文件夹的文件链接,可仿效代码(可不用单独刷新,如有增删,重新全部构建)。
    Sub test()
    Dim fso, fp, ar, ar1, subf, f, n%, m%, i%, j%, c As Range
    Sheets("6月").Activate
    ActiveSheet.UsedRange.Offset(2) = ""
    Set fso = CreateObject("scripting.filesystemobject")
    Set fp = fso.getfolder(ThisWorkbook.Path).subfolders
    ReDim ar(1 To fp.Count)
    For Each subf In fp
    n = n + 1
    If subf.Files.Count <> 0 Then
    ReDim ar1(1 To subf.Files.Count)
    m = 0
    For Each f In subf.Files
    m = m + 1
    ar1(m) = f & "|" & fso.getbasename(subf) & "|" & fso.getbasename(f)
    Next
    ar(n) = ar1
    End If
    Next
    For i = 1 To UBound(ar)
    If IsArray(ar(i)) Then
    Set c = Rows(1).Find(Split(ar(i)(1), "|")(1))
    If Not c Is Nothing Then
    For j = 1 To UBound(ar(i))
    With c.End(xlDown)
    .Offset(1).Value = Split(ar(i)(j), "|")(2)
    ActiveSheet.Hyperlinks.Add Anchor:=.Offset(1), Address:=Split(ar(i)(j), "|")(0)
    End With
    Next
    End If
    End If
    Next
    Set fso = Nothing
    End Sub

    点赞 打赏 评论

相关推荐 更多相似问题