Files & Folders from File Explorer Export to Excel - Code Issue

[bamaisgreat]

When I run the code code below which copies the files name, date and size to my worksheet I am having a couple issues. I believe the code is suppose to be putting them in date modified from newest to oldest but for some reason it is not showing up like that. Also im getting a run time error 1004 all of the sudden.

Sub ListFilesInFolder1()
Columns("A:E").ClearContents
    Dim FSO As Scripting.FileSystemObject
    Dim SourceFolder As Scripting.Folder
    Dim FileItem As Scripting.file

    SourceFolderName = "K:\Engineering\Job Release"

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)

    Range("A1:C1") = Array("File Name", "Date Last Modified", "File Size")

    i = 2
    For Each FileItem In SourceFolder.Files
        Cells(i, 1) = FileItem.Name
        Cells(i, 2) = FileItem.DateLastModified
        Cells(i, 3) = FileItem.Size
    

        i = i + 1
    Next FileItem

    Set FSO = Nothing


If ActiveSheet.AutoFilterMode = True Then
Else
ActiveSheet.Range("A1").AutoFilter
End If

    ActiveSheet.AutoFilter.Sort.SortFields.Clear
   ActiveSheet.AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("B1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[bobsan42]

I tried the code and in terms of functionality it works well.
The sorting is working properly - newest files are moved to the top.
I didn't get any RTE error-messages. Where do you get RTE-1004?

 

[bamaisgreat]

I tried the code and in terms of functionality it works well.
The sorting is working properly - newest files are moved to the top.
I didn't get any RTE error-messages. Where do you get RTE-1004?

Thanks for your response. The RTE reads " The sort reference is not valid, Make sure that its within the data you want to sort, and the first sort by box isnt the same or blank". When I select the Debug it highlights the code at the bottom of the script ".Apply"

 

[bobsan42]

maybe something's wrong with your autofilter. your rely on Excel to autodetect your data range properly, but something may have gone wrong.
I get no such error. And I cannot find a way to replicate the behaviour you talk about.
Try to inspect the objects and variables in the Locals Window in VBE while in debug mode and see if something's out of order.
For example check the sort range in the immediate window:

?ActiveSheet.AutoFilter.Sort.rng.address

Or check if some object is Nothing when it should be something.

 

[bamaisgreat]

maybe something's wrong with your autofilter. your rely on Excel to autodetect your data range properly, but something may have gone wrong.
I get no such error. And I cannot find a way to replicate the behaviour you talk about.
Try to inspect the objects and variables in the Locals Window in VBE while in debug mode and see if something's out of order.
For example check the sort range in the immediate window:

?ActiveSheet.AutoFilter.Sort.rng.address

Or check if some object is Nothing when it should be something.

ok. Thanks for the help.