List files and properties without FSO

tormodo

New Member
Joined
Sep 6, 2011
Messages
2
Hi.

I am trying to list files and their properties (need modified date and size) from a directory. I have tried using FileSystemObject, but retrieving the file name seems to take a lot of time (oddly the other properties are listed in a flash). It takes me several minutes to list just a couple of hundred files, but I need to compare about 3000 of them.

Is there an easy and efficient way to list many files and their properties?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
How are you using FSO?

I just used this function and it returned approx. 200 filenames in seconds.
Code:
Private Function AllFiles(ByVal FullPath As String) _
  As String()
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject
'PARAMETER: FullPath = FullPath to folder for
'which you want all files
'RETURN VALUE: An array containing a list of
'all file names in FullPath, or a 1-element
'array with an empty string if FullPath
'does not exist or it has no files
'REQUIRES: Reference to Micrsoft Scripting
'          Runtime
'EXAMPLE:
'Dim sFiles() as string
'dim lCtr as long
'sFiles = AllFiles("C:\Windows\System")
'For lCtr = 0 to Ubound(sFiles)
'  Debug.Print sfiles(lctr)
'Next

Dim oFs As New FileSystemObject
Dim sAns() As String
Dim oFolder As Folder
Dim oFile As File
Dim lElement As Long

If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)
 
    For Each oFile In oFolder.Files
      ReDim Preserve sAns(lElement)
      sAns(lElement) = oFile.Name & "," & oFile.DateLastModified & "," & oFile.Size
      
      lElement = lElement + 1
      
    Next
End If
 
AllFiles = sAns

ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function
 
Sub test()
Dim sfiles
Dim I As Long
    sfiles = AllFiles("C:\")
 
    For I = LBound(sfiles) To UBound(sfiles)
        Sheets(1).Range("A" & I + 1).Resize(, 3) = Split(sfiles(I), ",")
    Next I
 
End Sub
PS The function isn't my code, it has comments and error handling. I must have picked it up from somewhere.
 
Upvote 0
I tested both solutions and the one based on FSO processed my batch of 400 test files in about 7 secs, the other one used 2 secs.

Thanks for excellent help guys! :)
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top