如何使用Excel获取Windows目录中文件的属性

v7pvogib 于 2023-10-22 发布在 Windows

关注(0)|答案(1)|浏览(115)

我正试图为我的数字音乐收藏创建一个驾驶舱电子表格。我设法得到的音乐文件的名称与以下的安装代码：

Sub DirectoryFileLoop()
Dim fileDirectory As String
Dim fileName As String

fileDirectory = "D:\Sound\Jazz & Blues\"
fileName = Dir(fileDirectory)

i = 2
Do While Len(fileName) > 0
   Range("B" & i).Value = fileName
   i = i + 1
   fileName = Dir
Loop

End Sub

但是，我还需要获取文件的属性，如标题、贡献艺术家、年份等。例如，文件的名称为“Louis Prima - Closer to the Bone.flac”，标题属性为“Closer to the Bone”，贡献艺术家属性为“Louis Prima”，专辑属性为“野性的呼唤”，年份属性为“1957”，长度属性为“00：04：13”，修改日期属性为“20.08.2019 13：22”。我可以用我的代码获得name，但找不到任何东西来获得提到的其他属性。任何帮助都非常感谢。

excel

来源：https://stackoverflow.com/questions/77257642/how-can-i-get-properties-of-files-in-windows-directories-to-excel-using-vba

1条答案

按热度按时间

y53ybaqx1#

来自MrExcel的MVP Domenic在下面的链接中提供了一个解决方案。我对下面的代码做了一些调整，以显示所有属性。您可以稍后通过将index#放到我注解掉的varColumns中来指定列。
https://www.mrexcel.com/board/threads/meta-data-of-mp3-and-wav-files-to-excel.1091202/

Sub GetMetaDataFromSoundFiles()

    Dim objShellApp As Object
    Dim objFolder As Object
    Dim varColumns As Variant
    Dim arrData() As Variant
    Dim strFilename As String
    Dim fileCount As Long
    Dim i As Long
    Dim j As Long
    
    Set objShellApp = CreateObject("Shell.Application")
    Set objFolder = objShellApp.Namespace("C:\Your\audio\files\path") 'change the path to the source folder accordingly
    
    'varColumns = Array(0, 20, 21, 28, 16, 14) '  Limit colums to display
    
    ' Make display all
    ReDim varColumns(0 To 321)
    Dim k As Integer
    For k = 0 To 321
        varColumns(k) = k
    Next k
    ' comment out  5 lines above when you uncomment 'varColumns = Array(0, 20, 21, 28, 16, 14).
    
    
    ReDim arrData(0 To UBound(varColumns), 0 To objFolder.Items.Count)
    
    For i = LBound(arrData, 1) To UBound(arrData, 1)
        arrData(i, 0) = objFolder.GetDetailsOf(objFolder.Items, varColumns(i))
    Next i
    
    fileCount = 0
    For i = 0 To objFolder.Items.Count - 1
        strFilename = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), 0)
        If Right(strFilename, 4) = ".mp3" Or Right(strFilename, 4) = ".wav" Then
            fileCount = fileCount + 1
            For j = 0 To UBound(varColumns)
                arrData(j, fileCount) = objFolder.GetDetailsOf(objFolder.Items.Item(CLng(i)), varColumns(j))
            Next j
        End If
    Next i
    
    ActiveSheet.Range("A1").Resize(UBound(arrData, 2) + 1, UBound(arrData, 1) + 1).Value = Application.Transpose(arrData)
    
End Sub

赞(0）回复(0）举报 2023-10-22

我来回答

如何使用Excel获取Windows目录中文件的属性

1条答案

相关问题

热门标签

最新问答