How to work efficiently with the filesystemobject?

[TheSmileyCoder]

I am working on a app in which the start process is to collect information on all files stored within a folder (and its subfolders).

To that purpose I have started the following code, to look through the folder, and its subfolders:

Public Sub CollectInfo()
On Error GoTo err_Handler
   Dim t As Date
   t = Now()
   Dim fs As New FileSystemObject
   Dim myFolder As Folder
   Set myFolder = fs.GetFolder("G:\TestFiles")
   Dim rsFiles As DAO.Recordset
   Set rsFiles = CurrentDb.OpenRecordset("tbl_Files")
   Dim oFolder As Folder
   Dim oFile As File
   For Each oFolder In myFolder.SubFolders
      For Each oFile In oFolder.Files
 
         Dim s As String
         s = oFile.Name & oFile.Path
         'rsFiles.AddNew
         '   rsFiles!tx_FileName = oFile.Name
         '   rsFiles!tx_FilePath = oFile.Path
         'rsFiles.Update
 
 
 
      Next oFile
   Next oFolder
 
 
   MsgBox "Procedure completed in: " & Second(Now() - t)
exitSub:
   Set fs = Nothing
   Set oFile = Nothing
   Set oFolder = Nothing
   Set myFolder = Nothing
   Exit Sub
 
 
err_Handler:
   MsgBox Err.Number & Err.Description
   Resume exitSub
End Sub

I have commmented out the part where I intend to add the information to a table for further processing, since I wanted to focus on the efficiency of this step.

It takes the current program 41 seconds to collection information on 270 files in roughly 20 subfolders where the main folder is placed on a network drive.

I get the impression that the filesystemobject is calling the fileserver for the information alot of times, and was wondering if there was any way to get the information needed in a batch processing sort of way to speed it up.

Other speedup suggestions are appreciated as well!

[Rabbit]

Perhaps instead of a using file system object you could use a batch command and parse the output from that?

[TheSmileyCoder]

Interesting. I have not worked much with batch files. How would you go about getting the information from the batch file?

Can you have the information directly returned (like a function call) or would I need the batch file to write the information to a file and then read that file?

[Rabbit]

I found this: http://pastebin.com/CszKUpNS. It apparently allows you to redirect the output. I haven't tested it though so if you use it, let me know if it works.

[NeoPa]

You might also like to try working with the Dir() function. It's a little more basic but could probably work quite efficiently. Take care not to start a new Dir() run before the previous one has completed though, as each subsequent call is a follow-on from the previous call unless reset with new parameters.