VBA中的超链接循环

Question

我有一个问题，因为我想用文件夹的make超链接制作程序，我已经有了这个部分：

Dim a As String
Dim i As Long
Dim ark1 As Worksheet
Set ark1 = Arkusz1
For i = 1 To 3
    ark1.Cells(i, "A").Select
    a = "TR_" & i
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="C:/" & a, _
    SubAddress:="", ScreenTip:="a", TextToDisplay:=a
Next i
End Sub

但我有文件夹：TR_1_JonTR_2_阿利克斯等。并且有可能使excel只识别第一部分："TR_1_******"？

Answer 1

您可以使用以下代码解析子文件夹，然后将其用于超链接：

Public Sub Test()
    Set fso = CreateObject("Scripting.FileSystemObject")
    FolderName = "D:" 'Replace it with selected folder
    Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSOLibrary.GetFolder(FolderName)
    For Each oSubfolder In FSOFolder.SubFolders
            MsgBox oSubfolder
    Next oSubfolder
End Sub

编辑：

Public Sub NonRecursiveMethod()
    Set fso = CreateObject("Scripting.FileSystemObject")
    FolderName = "D:" 'Replace it with selected folder
    Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
    Set FSOFolder = FSOLibrary.GetFolder(FolderName)
    i = 1
    For Each oSubfolder In FSOFolder.SubFolders
            ActiveSheet.Cells(i, "A") = oSubfolder.Name
            Cells(i, "A").Hyperlinks.Add Anchor:=Cells(i, "A"), Address:=oSubfolder, SubAddress:=""
            i = i + 1
    Next oSubfolder
End Sub