按文件创建日期的顺序将文件夹中的文件名打印到Excel工作表 [英] Print file names in folder to Excel Sheet in order of file creation date

问题描述

您好，我有一个程序可以将在文件夹中找到的文件名打印到excel工作表中，但是我想知道是否可以对其进行修改，以便按文件修改日期对文件夹中的文件进行排序(例如在资源管理器中)，然后按该顺序将文件名打印到工作表.任何帮助，将不胜感激！

Hi there I have a procedure that prints file names found in a folder to an excel sheet, but I wonder if it could be modified so that it sorts the files in the folder by file Date Modified (like in the explorer) first and then prints the file names in that order to the sheet. Any help would be appreciated!

Sub HGDW_PrintFiles()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim i As Integer

    'Create an object of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Get the folder object
    Set objFolder = objFSO.Getfolder("C:\Users\bf91955\Desktop\Test\")
    i = 1

    'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
        'print file name to column a
        Cells(i, 1) = objFile.Name
        i = i + 1
    Next objFile
End Sub

推荐答案

这会将其读入数组，然后在输出之前对数组进行冒泡排序.这里的排序发生在VBA数组中，应该比工作表范围中的排序要快.

This would read it into an array and then bubble sort the array before output. The sorting here happens in the VBA array which should be faster than a sorting in the worksheet range.

Sub ReadFiles()
    Dim strFolder As String
    Dim fso As Object
    Dim fld As Object
    Dim fil As Object
    Dim arrNames() As String
    Dim arrDates() As Date
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim strTmp As String
    Dim dtmTmp As Date

    Set fso = CreateObject("Scripting.FileSystemObject")
    ' Modify as needed
    strFolder = "C:\"
    Set fld = fso.GetFolder(strFolder)

    ' Set up arrays
    n = fld.Files.Count
    ReDim arrNames(1 To n)
    ReDim arrDates(1 To n)

    ' Fill arrays
    For Each fil In fld.Files
        i = i + 1
        arrNames(i) = fil.Name
        arrDates(i) = fil.DateLastModified
    Next fil

    ' Bubble sort descending on date
    For i = 1 To n - 1
        For j = i + 1 To n
            If arrDates(i) < arrDates(j) Then 'to sort ascending change < to >
                dtmTmp = arrDates(i)
                arrDates(i) = arrDates(j)
                arrDates(j) = dtmTmp
                strTmp = arrNames(i)
                arrNames(i) = arrNames(j)
                arrNames(j) = strTmp
            End If
        Next j
    Next i

    ' Do something with the arrays, e.g.
    For i = 1 To n
        Debug.Print arrNames(i)
    Next i
End Sub

这篇关于按文件创建日期的顺序将文件夹中的文件名打印到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！