列出Excel中链接的文件名 [英] List file names with links in excel

问题描述

有没有办法在excel表格中列出不同目录中的文件名，以及链接（所以可以点击文件名旁边的链接，文件打开）？

Is there a way to list file names across different directories in an excel sheet, with links (so one can click on the link beside the file name and the file would open)?

• 我准备好编写脚本，但我不知道任何特定的方法或命令。


• 我不想


• 我不在乎目录/树结构，但文件链接应该存在。


• 有一个目录包含很多其他文件夹，其中包含我需要列出的文件，主要是* .pdf的。

• I'm ready to write a script, but I don't know of any particular method or command.
• I don't want to be typing the whole thing.
• I don't care about directory/tree structure, but file link should be present.
• There is one directory which contains lots of other folders which contain the files I need to list, mostly *.pdf's.

任何帮助赞赏。谢谢！

推荐答案

您可以使用下面的代码获取文件名和文件路径将主文件夹设置为strFolderPath

You can use below code to get File name and File path Set main folder to strFolderPath

'Global Declaration for Start Row

Public lngRow As Long

Sub pReadAllFilesInDirectory()

    Dim strFolderPath               As String
    Dim BlnInclude_subfolder        As Boolean

    'Set Path here
    strFolderPath = "C:\"

    'set start row
    lngRow = 1

    'Set this true if you want list of sub-folders as well
    BlnInclude_subfolder = True

    '---------- Reading of files in folders and sub-folders------
    Call ListMyFiles(strFolderPath, BlnInclude_subfolder)
    '---------- Reading of files in folders and sub-folders------

End Sub

Sub ListMyFiles(mySourcePath As String, blnIncludeSubfolders As Boolean)

    Dim MyObject            As Object
    Dim mySource            As Object
    Dim mySubFolder         As Object
    Dim myfile              As Object
    Dim iCol                As Long

    Set MyObject = CreateObject("Scripting.FileSystemObject")
    Set mySource = MyObject.GetFolder(mySourcePath)

    'Loop in each file in Folder
    For Each myfile In mySource.Files

        iCol = 1
        Sheet1.Cells(lngRow, iCol).Value = myfile.Name  'File Name
        iCol = iCol + 1
        Sheet1.Cells(lngRow, iCol).Value = myfile.Path  'File Path/Location
        lngRow = lngRow + 1

    Next

    If blnIncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If

End Sub

这篇关于列出Excel中链接的文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！