File Find And Open Speed

[FractalWalk]

I am using Excel VBA and the FileScriptingObject to open, read and close all text files in a folder. The script works great on my local drive but when I apply it to the network it slows to a crawl. I expected it to be slower, but it is taking over 15 times as long.

Here is the relevant code:

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(df & d1) Then
Set fil = Nothing
Set fld = fs.GetFolder(df & d1)
For Each fil In fld.Files
Open fil For Input As #1

           Close
        Next fil
    End If

On my local drive it takes 18 seconds to find, open and close all the files. On the shared drive it takes 243 seconds. Is that typical or is there anything I can do to speed this process up?

 

[HughLerwill]

I wonder if it is the For loop or the Open/ Close which is slowing things down; have you tried remming the latter?

Code in and around

http://vbnet.mvps.org/code/fileapi/fsoapicompare.htm

could be interesting for you.

 

[FractalWalk]

I tested both.

Just the For loop without the open was 3 seconds on the local drive and 90 seconds on the shared drive. Adding the Open was the 18 and 243 respectively.

 

[HughLerwill]

Another point worth considering does df & d1 describe a UNC folder path or a mapped drive folder path, I believe the latter can be faster.

 

[FractalWalk]

It was originally UNC. I changed it to the mapped path and got similar results.

 

[HughLerwill]

So what is the net connection like? Wired or Wireless?

 

[FractalWalk]

It's a wired LAN connection. I have no idea what the speed is, but it is the standard company pipe to provide e-mail, interent and shared drive access.

 

[HughLerwill]

Not sure if it will have any effect but I'd prefer;

Dim f as integer
f = FreeFile
Open fil For Input As f

Close f

Have you checked out any of Randy's code yet?