"Scripting.FileSystemObject" problem

[Bob Phillips]

How imperfect do you want?

Sometimes, it returns a smaller number of files than it should. Sometimes, that smaller number is 0.

BTW, MS have acknowledged the failings of FileSearch, and dropped it from Excel 2007.

[malik641]

Thanks for the code sample Matt.

It is used for a variety of things, but one you can actually see is that any program or executable in those folders can be called without qualifying the path name. So it you have "runme.exe" in c:\windows\system32\wbem, going to start/run and typing "runme" will execute that program. The path is often set in the autoexec.bat file processed when the pc starts
Matt

Is there any good documented information you know about so that I can read about it?

How imperfect do you want?

As imperfect as it gets!

BTW, MS have acknowledged the failings of FileSearch, and dropped it from Excel 2007.

Do you know if they will be replacing it with a new function?

And Bob, does the same imperfection you mentioned happen with Dir()?

[mvidas]

Is there any good documented information you know about so that I can read about it?

Strangely I really couldn't find anything more than what I put above, just worded differently. One such example:

The system path is a list of folders, separated by a semicolon, that identifies the folders that the system should search when looking for files that are called from the Run dialog box, command line, or other processes.

does the same imperfection you mentioned happen with Dir()?

The Dir method in vb should not have the same thing as above, unless the file just doesn't exist. The Dir method is the same as if you went to a command line and typed "dir", except it returns a single file rather than the list of files. I'd be curious to hear if it did have any imperfections like the filesearch method

[SamT]

This from "Inside MS-DOS 6"

PATH tells DOS where to look for a program or batch file in the event that file can't be found in the current directory."

Another way of looking at it;

A path is the drive designator ( C: ) and all the folder names down to the file, concantenated together with a '\' (Backslash.)

On my computor the path to excel.exe is

"C:\Program Files\Microsoft Office\Office"

Its' Dir is slightly different

"C:\Program Files\Microsoft Office\Office\"

And the fully qualified name of the excel program is

"C:\Program Files\Microsoft Office\Office\excel.exe"

Actual use of any of these will require double quotes because of some spaces in the string and because some of the folder names are longer than 8 characters

[Zack Barresse]

BTW, MS have acknowledged the failings of FileSearch, and dropped it from Excel 2007.

I was sad to see when they did this. I thought they should have worked on it and made it more reliable/robust, but they took the easy way out and just dropped it.

@Joseph: They didn't replace it with anything (AFAIK), which would mean you're either going to use the Dir() or FSO method.

[Bob Phillips]

I was sad to see when they did this. I thought they should have worked on it and made it more reliable/robust, but they took the easy way out and just dropped it.

@Joseph: They didn't replace it with anything (AFAIK), which would mean you're either going to use the Dir() or FSO method.

Why? Who needs 3 ways of doing it? If it's broke, and there are alternatives, ditch it. Makes sense to me.

[malik641]

Why? Who needs 3 ways of doing it? If it's broke, and there are alternatives, ditch it. Makes sense to me.

When FileSearch did work, it seemed to be a lot faster than FSO (not sure about Dir() though)


And thanks SamT for the explanation

[Zack Barresse]

Why you ask? Because I like having a native method designed and built especially for the task at hand. The Dir() function was not intentionally designed (at least I don't think so) for looping through folders and subfolders, plus it's not always the best choice as it can fail under certain conditions. The FSO method is great and I love it, but it's dependent on creating objects from a dynamic link library, which I don't mind but is not always the best solution (IMO).

I admit, I tend to use FSO more than anything else because I find it easier and more comfortable. I was more comfortable knowing there was a native method for exactly what I wanted to do. Drop it? No, by all means do not drop it just because it has some shortcomings. By that very same logic there would be no more methods to which this could be done.

[mvidas]

When FileSearch did work, it seemed to be a lot faster than FSO (not sure about Dir() though)

The setup:[vba]Option Explicit
Private FSO As Object
Sub SpeedTestCheckCDrive()
Dim AnArrayDir() As String, AnArrayFSO() As String, i
Dim Timer1 As Double, Timer2 As Double
ReDim AnArrayFSO(0)
ReDim AnArrayDir(0)
Timer1 = Timer
Set FSO = CreateObject("scripting.filesystemobject")
ReturnAllFilesUsingFSO "C:\", AnArrayFSO
Set FSO = Nothing
Timer1 = Timer - Timer1
Timer2 = Timer
ReturnAllFilesUsingDir "C:\", AnArrayDir
Timer2 = Timer - Timer2
Debug.Print "FSO took " & Timer1 & " seconds, " & UBound(AnArrayFSO) + 1 & " files found"
Debug.Print "DIR took " & Timer2 & " seconds, " & UBound(AnArrayDir) + 1 & " files found"
End Sub
Function ReturnAllFilesUsingDir(ByVal vPath As String, ByRef vsArray() As String) As Boolean
Dim tempStr As String, vDirs() As String, Cnt As Long, dirCnt As Long
If Len(vsArray(0)) = 0 Then
Cnt = 0
Else
Cnt = UBound(vsArray) + 1
End If
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
On Error GoTo BadDir
tempStr = Dir(vPath, 31)
Do Until Len(tempStr) = 0
If Asc(tempStr) <> 46 Then
If GetAttr(vPath & tempStr) And vbDirectory Then
ReDim Preserve vDirs(dirCnt)
vDirs(dirCnt) = tempStr
dirCnt = dirCnt + 1
End If
BadDirGo:
End If
tempStr = Dir
SkipDir:
Loop
On Error GoTo BadFile
tempStr = Dir(vPath, 15)
Do Until Len(tempStr) = 0
ReDim Preserve vsArray(Cnt)
vsArray(Cnt) = vPath & tempStr
Cnt = Cnt + 1
tempStr = Dir
Loop
BadFileGo:
On Error GoTo 0
If dirCnt > 0 Then
For dirCnt = 0 To UBound(vDirs)
If Len(Dir(vPath & vDirs(dirCnt))) = 0 Then
ReturnAllFilesUsingDir vPath & vDirs(dirCnt), vsArray
End If
Next
End If
Exit Function
BadDir:
If tempStr = "pagefile.sys" Or tempStr = "???" Then
' Debug.Print "DIR: Skipping: " & vPath & tempStr
Resume BadDirGo
ElseIf Err.Number = 52 Then
' Debug.Print "No read rights: " & vPath & tempStr
Resume SkipDir
End If
Debug.Print "Error with DIR: " & Err.Number & " - " & Err.Description
Exit Function
BadFile:
If Err.Number = 52 Then
' Debug.Print "No read rights: " & vPath & tempStr
Else
Debug.Print "Error with DIR: " & Err.Number & " - " & Err.Description
End If
Resume BadFileGo
End Function
Function ReturnAllFilesUsingFSO(ByVal vPath As String, ByRef vsArray() As String) As Boolean
Dim f As Object, fld As Object, Cnt As Long
Set fld = FSO.GetFolder(vPath)
If Len(vsArray(0)) = 0 Then
Cnt = 0
Else
Cnt = UBound(vsArray) + 1
End If
On Error GoTo UhOhs
For Each f In fld.Files
ReDim Preserve vsArray(Cnt)
vsArray(Cnt) = f.path
Cnt = Cnt + 1
Next
For Each f In fld.SubFolders
ReturnAllFilesUsingFSO f.path, vsArray
Next
LaterGater:
Set f = Nothing
Set fld = Nothing
Exit Function
UhOhs:
If Err.Number = 70 Then
' Debug.Print "Permission denied: " & vPath
Else
Debug.Print "Error with FSO: " & Err.Number & " - " & Err.Description
End If
Resume LaterGater
End Function[/vba]

FSO took 46.1400000000026 seconds, 36102 files found
DIR took 7.6870000000032 seconds, 36102 files found

Matt

[malik641]

Error with DIR: 5 - Invalid procedure call or argument
FSO took 70.9949999999965 seconds, 62771 files found
DIR took 1.60000000011635E-02 seconds, 1 files found

Not sure why I got the error...(error from my work computer)

I'm going to have to look at this later (so I can fully understand what's going on), but well done

Could you add the Application.FileSearch method as part of your procedure to compare?


And by the way, about the original problem, it has to wait until this weekend because I don't have time to get with my friend (test on Friday...been studying since monday )

[mvidas]

Hmmmm... odd!

I only got the error 5 when it was checking a directory I didnt have access to (like C:\docs&settings\Administrator).

At the end of "return all files using dir", add "or err.number=5" to the 52 lines, like:[vba]' ElseIf Err.Number = 52 Then
ElseIf Err.Number = 52 Or Err.Number = 5 Then
'and
' If Err.Number = 52 Then
If Err.Number = 52 Or Err.Number = 5 Then[/vba]See if that gets rid of it.

Another thing you could try (in place of the above if you're not getting anywhere) could be to change the two lines of[vba]Debug.Print "Error with DIR: " & Err.Number & " - " & Err.Description[/vba]and identify the errorchecking part it is in, like[vba]Debug.Print "Error with DIR (BadDir): " & Err.Number & " - " & Err.Description
'and
Debug.Print "Error with DIR (BadFile): " & Err.Number & " - " & Err.Description[/vba]
The fact that you didnt get any results (despite it saying 1, just because of the +1 after ubound) makes me wonder if perhaps this line somehow got messed up in the copy/paste:[vba] ReturnAllFilesUsingDir "C:\", AnArrayDir[/vba]Matt

[mvidas]

Could you add the Application.FileSearch method as part of your procedure to compare?

Woops, missed this part. Sure let me add it (though I've never used it, it shouldnt be tough to figure out, thats the point of it, right?)
I think I'll add the API filesearches as well

[mvidas]

Updated code (no API yet):
[vba]Option Explicit
Private FSO As Object
Sub SpeedTestCheckCDrive()
Dim AnArrayDir() As String, AnArrayFSO() As String, i As Long, FSCnt As Long
Dim Timer1 As Double, Timer2 As Double, Timer3 As Double, Timer4 As Double
ReDim AnArrayFSO(0)
ReDim AnArrayDir(0)

Timer1 = Timer
Set FSO = CreateObject("scripting.filesystemobject")
ReturnAllFilesUsingFSO "C:\", AnArrayFSO
Set FSO = Nothing
Timer1 = Timer - Timer1
DoEvents
Timer2 = Timer
ReturnAllFilesUsingDir "C:\", AnArrayDir
Timer2 = Timer - Timer2
DoEvents
Timer3 = Timer
FSCnt = ReturnFileCountUsingFileSearch("C:\")
Timer3 = Timer - Timer3

Debug.Print "FSO took " & Timer1 & " seconds, " & UBound(AnArrayFSO) + 1 _
& " files found"
Debug.Print "DIR took " & Timer2 & " seconds, " & UBound(AnArrayDir) + 1 _
& " files found"
Debug.Print "FileSearch took " & Timer3 & " seconds, " & FSCnt & " files found"
End Sub
Function ReturnAllFilesUsingDir(ByVal vPath As String, ByRef vsArray() As String) _
As Boolean
Dim tempStr As String, vDirs() As String, Cnt As Long, dirCnt As Long
If Len(vsArray(0)) = 0 Then
Cnt = 0
Else
Cnt = UBound(vsArray) + 1
End If
If Right(vPath, 1) <> "\" Then vPath = vPath & "\"
On Error GoTo BadDir
tempStr = Dir(vPath, 31)
Do Until Len(tempStr) = 0
If Asc(tempStr) <> 46 Then
If GetAttr(vPath & tempStr) And vbDirectory Then
ReDim Preserve vDirs(dirCnt)
vDirs(dirCnt) = tempStr
dirCnt = dirCnt + 1
End If
BadDirGo:
End If
tempStr = Dir
SkipDir:
Loop
On Error GoTo BadFile
tempStr = Dir(vPath, 15)
Do Until Len(tempStr) = 0
ReDim Preserve vsArray(Cnt)
vsArray(Cnt) = vPath & tempStr
Cnt = Cnt + 1
tempStr = Dir
Loop
BadFileGo:
On Error GoTo 0
If dirCnt > 0 Then
For dirCnt = 0 To UBound(vDirs)
If Len(Dir(vPath & vDirs(dirCnt))) = 0 Then
ReturnAllFilesUsingDir vPath & vDirs(dirCnt), vsArray
End If
Next
End If
Exit Function
BadDir:
If tempStr = "pagefile.sys" Or tempStr = "???" Then
' Debug.Print "DIR: Skipping: " & vPath & tempStr
Resume BadDirGo
ElseIf Err.Number = 52 Then 'or err.number=5 then
' Debug.Print "No read rights: " & vPath & tempStr
Resume SkipDir
End If
Debug.Print "Error with DIR (BadDir): " & Err.Number & " - " & Err.Description
Debug.Print " vPath: " & vPath
Debug.Print " tempStr: " & tempStr
Exit Function
BadFile:
If Err.Number = 52 Then 'or err.number=5 then
' Debug.Print "No read rights: " & vPath & tempStr
Else
Debug.Print "Error with DIR (BadFile): " & Err.Number & " - " & Err.Description
Debug.Print " vPath: " & vPath
Debug.Print " tempStr: " & tempStr
End If
Resume BadFileGo
End Function
Function ReturnAllFilesUsingFSO(ByVal vPath As String, ByRef vsArray() As String) _
As Boolean
Dim f As Object, fld As Object, Cnt As Long
Set fld = FSO.GetFolder(vPath)
If Len(vsArray(0)) = 0 Then
Cnt = 0
Else
Cnt = UBound(vsArray) + 1
End If
On Error GoTo UhOhs
For Each f In fld.Files
ReDim Preserve vsArray(Cnt)
vsArray(Cnt) = f.path
Cnt = Cnt + 1
Next
For Each f In fld.SubFolders
ReturnAllFilesUsingFSO f.path, vsArray
Next
LaterGater:
Set f = Nothing
Set fld = Nothing
Exit Function
UhOhs:
If Err.Number = 70 Then
' Debug.Print "Permission denied: " & vPath
Else
Debug.Print "Error with FSO: " & Err.Number & " - " & Err.Description
End If
Resume LaterGater
End Function
Function ReturnFileCountUsingFileSearch(ByVal vPath As String) As Long
'just using this count for a speed test, since most people wouldnt want
' the results in an array
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.LookIn = vPath
.FileType = msoFileTypeAllFiles
' .FileName = "*.*"
If .Execute > 0 Then
ReturnFileCountUsingFileSearch = .FoundFiles.Count
End If
End With
End Function[/vba]

FSO took 29.6239999999985 seconds, 36070 files found
DIR took 10.6090000000024 seconds, 36070 files found
FileSearch took 47.7339999999984 seconds, 35661 files found

FSO took 32.3739999999976 seconds, 36070 files found
DIR took 7.9839999999982 seconds, 36070 files found
FileSearch took 34.9050000000015 seconds, 35661 files found

Didnt bother sending the results to a text file or anything to see what files weren't included. Can anyone who uses filesearch regularly tell me if my function could be improved/sped up?

[mvidas]

Interesting.. it appears that getting the filenames from FSO is what takes the longest. I changed the code on all (and added the API method) to only return file counts from the search. Rather than paste it, I'm just going to attach the exported module, but here were the results (again, simply a count):

FSO took 4.92199999999808 seconds, 36314 files found
DIR took 7.23399999999942 seconds, 36314 files found
FileSearch took 55.4210000000027 seconds, 35905 files found
APIs took 11.9990000000006 seconds, 36313 found

[mvidas]

Weird.. application.filesearch returned a result to me, at least a count. Now when I'm looping through the results to add to an array, I got a 'permission denied' error on it, even when I just hover over .foundfiles(i)
Interesting, filesearch can see it, but my access privileges prevent me from seeing it. Apparently it only happened 4 times on my C:\, but still odd nonetheless that the filesearch method has more access than the currentuser.

Uhhh, now that I got past that I'm getting an "Out of Memory" error?! When I highlight the .FoundFiles(i) again I'm seeing Out of Memory. Should I be using anything like ".FoundFiles(i).FileName" or .Name or anything?
Cant be a huge deal as the .foundfiles.count at the start was 36020, and only 36007 items in the resulting array ... could this be another 'imperfection' of using filesearch?

OK, last part of my thread clutter. Changed each to return a resulting array:

FSO took 49.8429999999996 seconds, 36429 files found
DIR took 13.7960000000015 seconds, 36429 files found
FileSearch took 55.8890000000002 seconds, 36007 files found
APIs took 26.9530000000031 seconds, 36428 files found
'** CLEARED INET CACHE **'
FSO took 29.6090000000002 seconds, 36012 files found
DIR took 7.50000000000023 seconds, 36012 files found
FileSearch took 41.2490000000002 seconds, 35590 files found
APIs took 19.9059999999968 seconds, 36011 files found

My findings:
-dir is fast
-fso is faster if you just need file counts
-filesearch is slow whether you need filename array or not (iterating through results doesnt add much runtime)
-these apis are fun
-internet cache takes a long time to look through
QED, for now

final module attached