vba fastest and most reliable way to list files

[hobgoblinvasya]

Hi all,

as said in the subject im looking for your opinion on what is the fastest and most reliable way to list files within a folder?

what i need is to get the file name and file size and place it into 2 columns in excel. i also need to make sure that the code will not break if one of the files in the folder is being modified while the code is running. i need it to not lock up the files. and finally i need it to be fairly fast.

right now im using "Scripting.FileSystemObject",

    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim SourceFolder
    Set SourceFolder = FSO.GetFolder("MyDirectory")
    'Set SourceFolder = CreateObject("Scripting.Folder")
    
    Dim FileItem

    For Each FileItem In SourceFolder.Files
         'do my thing
    Next FileItem

and while it is decent enough it takes anywhere from 1m 30sec to 5+ minutes to list the same 500 files. i would definitely like to speed it up to 15-20 seconds range.

edit: forgot to mention that all files are rather small in size (<75kb), so its not a size problem.

thanks in advance !
cheers

 

[bertie]

I normally use the Dir() function when working with files or folders.

For testing the FSO procedure against the Dir() function I copied some 500+ XLs and txt files. Filesize of the excel files < 72kb.

Runtime for FSO = 0.265625 seconds
Runtime for Dir() = 0.171825 seconds

The procedures I used are listed below:

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] testFSO()
   [COLOR=darkblue]Dim[/COLOR] FSO
   [COLOR=darkblue]Dim[/COLOR] myDirectory
   [COLOR=darkblue]Dim[/COLOR] counter
   [COLOR=darkblue]Dim[/COLOR] startTime
   [COLOR=darkblue]Dim[/COLOR] endTime
   [COLOR=darkblue]Dim[/COLOR] runTime
   [COLOR=darkblue]Dim[/COLOR] FileItem
 
   myDirectory = "C:\temp2\"
    [COLOR=darkblue]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
 
    [COLOR=darkblue]Dim[/COLOR] SourceFolder
    [COLOR=darkblue]Set[/COLOR] SourceFolder = FSO.GetFolder(myDirectory)
    [COLOR=green]'Set SourceFolder = CreateObject("Scripting.Folder")[/COLOR]
   startTime = Timer
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] FileItem [COLOR=darkblue]In[/COLOR] SourceFolder.Files
         counter = counter + 1
         Sheets("sheet1").Cells(counter, 1) = FileItem.Name
         Sheets("sheet1").Cells(counter, 2) = FileItem.Size
    [COLOR=darkblue]Next[/COLOR] FileItem
   endTime = Timer
   runTime = endTime - startTime
 
   Sheets("sheet1").Cells(counter + 1, 1) = runTime
   [COLOR=darkblue]Set[/COLOR] FSO = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
[COLOR=darkblue]Sub[/COLOR] testDIR()
   [COLOR=green]'''''''''''''''''''''''''''''''''''''[/COLOR]
   [COLOR=green]'test 2 using dir()[/COLOR]
   [COLOR=green]'''''''''''''''''''''''''''''''''''[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] myFile
   [COLOR=darkblue]Dim[/COLOR] myDirectory
   [COLOR=darkblue]Dim[/COLOR] counter
   [COLOR=darkblue]Dim[/COLOR] startTime
   [COLOR=darkblue]Dim[/COLOR] endTime
   [COLOR=darkblue]Dim[/COLOR] runTime
   myDirectory = "C:\temp2\"
 
   myFile = Dir(myDirectory & "*.*")
   counter = 0
   startTime = Timer
 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] myFile <> ""
      counter = counter + 1
      [COLOR=darkblue]With[/COLOR] myFile
         Sheets("sheet1").Cells(counter, 4) = myFile
         myFile = myDirectory & myFile
         Sheets("sheet1").Cells(counter, 5) = FileLen(myFile)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      myFile = Dir()
   [COLOR=darkblue]Loop[/COLOR]
 
   endTime = Timer
   runTime = endTime - startTime
   Sheets("sheet1").Cells(counter + 1, 4) = runTime
[COLOR=darkblue]End[/COLOR] Sub

 

[hobgoblinvasya]

hi, this is an awesome approach ! granted, i haven't tested it extensively (i.e. when a user is editing one of the file), but so far it is light years faster then with FSO ! So a huge thanks to you !

 

[Norie]

You could write a small batch file that sends all the filenames, and other information, to a text file using a pipe.

When I say small I mean small - it would probably be one line of code.

The text file generated could then be easily opened in Excel.

 

[hobgoblinvasya]

hey norie, i wish i could do that , but my file is used as a dashboard monitoring more then one issue. so having a separate file is not really an option.

what i was originally concerned was the fact that the whole routine took up to 5 minutes to complete. with bertie's code it now takes 3-5 seconds. a HUGE gain ! so i will just leave it at that and see how it runs after a while.

in any case, thanks guy for all your help. wish i can give some back (im trying on officeexperts site )

Cheers !

 

[Norie]

You could actually do it without creating a file, but I don't see why creating a file would be a problem - am I missing something.

I'm sure Bertie's code works fine but I always kind of get alarm bells when I see code that uses 'non-native' Excel functionality.

 

[hobgoblinvasya]

tbh, i wouldnt know where to begin with batch files (i have never created any, so i would have even more trouble then i had so far).

as far as non native excel's functionality, i may be wrong, but the "dir" example uses ONLY stuff that is available in excel. (or comes with it by default since before i had my first computer :P)

 

[Norie]

I wasn't referring to Dir - that is a 'native' function, but FSO isn't.

I'm no expert in batch files or DOS commands myself but I do know that with one line you could list all the files in a directory.

In fact that would involve using the DOS command Dir.

 

[hobgoblinvasya]

hehe, then Dir it is. since im much more familiar with VB i will stay with bertie's method. but if it fails (impossible ) i will definitely be back harrassing you about the batch way. hehe... :D

thanks again to all of you. you can now probably mark this thread as solved.

cheers !