利用Excel VBA自動產生您所設定路徑向下之檔案名稱(file list)並自動加入超連結Hyper Link

想要自動產生一file list並自動加入HyperLink，只要mouse click就可以自動開啟該檔，如此之需求可能有很多方式來實作出來。

本例是使用Excel VBA的方式來做，以下是Code，感謝很多網友熱心PO文，才能讓我參考後完成這個小小功能，所以我也PO出來，請參考。

Dim iRow
Dim fso As Object

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, iCol), Address:= _
        myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
  
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

下載Excel 檔