By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

How to work efficiently with the filesystemobject?

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub CollectInfo()
  2. On Error GoTo err_Handler
  3.    Dim t As Date
  4.    t = Now()
  5.    Dim fs As New FileSystemObject
  6.    Dim myFolder As Folder
  7.    Set myFolder = fs.GetFolder("G:\TestFiles")
  8.    Dim rsFiles As DAO.Recordset
  9.    Set rsFiles = CurrentDb.OpenRecordset("tbl_Files")
  10.    Dim oFolder As Folder
  11.    Dim oFile As File
  12.    For Each oFolder In myFolder.SubFolders
  13.       For Each oFile In oFolder.Files
  14.  
  15.          Dim s As String
  16.          s = oFile.Name & oFile.Path
  17.          'rsFiles.AddNew
  18.          '   rsFiles!tx_FileName = oFile.Name
  19.          '   rsFiles!tx_FilePath = oFile.Path
  20.          'rsFiles.Update
  21.  
  22.  
  23.  
  24.       Next oFile
  25.    Next oFolder
  26.  
  27.  
  28.    MsgBox "Procedure completed in: " & Second(Now() - t)
  29. exitSub:
  30.    Set fs = Nothing
  31.    Set oFile = Nothing
  32.    Set oFolder = Nothing
  33.    Set myFolder = Nothing
  34.    Exit Sub
  35.  
  36.  
  37. err_Handler:
  38.    MsgBox Err.Number & Err.Description
  39.    Resume exitSub
  40. 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!
May 29 '12 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Perhaps instead of a using file system object you could use a batch command and parse the output from that?
May 29 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
May 29 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
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.
May 29 '12 #4

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 2 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.