Files & Folders from File Explorer Export to Excel

[bamaisgreat]

I have an excel sheet named “Eng Q” that I am needing only the files and folders put into from Z:engineering/program. Just a list of what’s in that main folder. Is this something that can be obtained when I open the excel sheet. Just need file name, date and file size to show up in columns A,B and C.

 

[AC PORTA VIA]

is this what you are looking for

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

    SourceFolderName = "Z:engineering/program"

    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

End Sub

 

[bamaisgreat]

Thank you. Does this go in the the worksheet code or does it run by activating a macro?

 

[AC PORTA VIA]

this one will need to be placed in regular module
if you want to go in sheet module use below code
you will need to enable "Microsoft Scripting Runtime" also for code to work

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

    SourceFolderName = "Z:engineering/program"

    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
End Sub

 

[bamaisgreat]

this one will need to be placed in regular module
if you want to go in sheet module use below code
you will need to enable "Microsoft Scripting Runtime" also for code to work

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

    SourceFolderName = "Z:engineering/program"

    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
End Sub

I tried inserting the first code you sent into a module and running it and I got a Compile error: User-defined type not defined. On the line with FSO As Scripting.FileSystemObject

 

[AC PORTA VIA]

you will need to enable "Microsoft Scripting Runtime" for code to work ( I forgot to tell you that in the first post)
in vba go to tools-References and select "Microsoft Scripting Runtime"

 

[bamaisgreat]

you will need to enable "Microsoft Scripting Runtime" for code to work ( I forgot to tell you that in the first post)
in vba go to tools-References and select "Microsoft Scripting Runtime"

That worked thanks. Is there something that could be added so they would show up fromdate modified newest to oldest.

 

[AC PORTA VIA]

Add below to the end of your code

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

 

[bamaisgreat]

Add below to the end of your code

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

Thanks again. Works great