Previous Topic Next Topic

FileName, Size and TimeStamp

Closed
yelli - Apr 15, 2010 at 09:09 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Apr 15, 2010 at 03:15 PM
Hi All,

I have very limited knowledge of DOS Scripting but would like to know if it is possible to create a script that retrieve the file name, size and timestamp in a directory and subdirectories and output the data to a spreadsheet?


Thanks for taking the time to answer this.

Yelli

Kind regards,



2 replies

Answer 1 / 2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Apr 15, 2010 at 11:23 AM
It would be far easier to do so in VB script.
0
yelli
Apr 15, 2010 at 01:02 PM
Hi Rizvisa1

how will you do such in VB Script?

thanks,

Yelli
0
Answer 2 / 2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Apr 15, 2010 at 03:15 PM
Insert this in a module of excel and execute TestListFilesInFolder. But before that change the value
sStartAtDir = "C:\Users\haadi\Desktop\" 



Sub TestListFilesInFolder()
Dim sStartAtDir As String

    sStartAtDir = "C:\Users\haadi\Desktop\"
    
    With Range("1:1")
        .Font.Bold = True
        .Font.Size = 12
    End With
    
    Range("A1").Formula = "File Path:"
    Range("B1").Formula = "File Name:"
    Range("C1").Formula = "File Size:"
    Range("D1").Formula = "File Type:"
    Range("E1").Formula = "Date Created:"
    Range("F1").Formula = "Date Last Accessed:"
    Range("G1").Formula = "Date Last Modified:"
    Range("H1").Formula = "Attributes:"
    Range("I1").Formula = "Short File Path:"
    Range("J1").Formula = "Short File Name:"
    
    ListFilesInFolder sStartAtDir, True

End Sub

Sub ListFilesInFolder(SourceFolderName As String, Optional IncludeSubfolders As Boolean = False)
Dim FSO As Object
Dim SourceFolder As Object
Dim SubFolder As Object
Dim FileItem As Object

Dim lRowBeanCounter As Long

    lRowBeanCounter = 2
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
   
    For Each FileItem In SourceFolder.Files

        Cells(lRowBeanCounter, 1).Formula = FileItem.Path
        Cells(lRowBeanCounter, 2).Formula = FileItem.Name
        Cells(lRowBeanCounter, 3).Formula = FileItem.Size
        Cells(lRowBeanCounter, 4).Formula = FileItem.Type
        Cells(lRowBeanCounter, 5).Formula = FileItem.DateCreated
        Cells(lRowBeanCounter, 6).Formula = FileItem.DateLastAccessed
        Cells(lRowBeanCounter, 7).Formula = FileItem.DateLastModified
        Cells(lRowBeanCounter, 8).Formula = FileItem.Attributes
        Cells(lRowBeanCounter, 9).Formula = FileItem.ShortPath
        Cells(lRowBeanCounter, 10).Formula = FileItem.ShortName
        
        lRowBeanCounter = lRowBeanCounter + 1 ' next row number
    
    Next FileItem
    
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    
    Columns("A:J").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing

End Sub
0