.filesearch trouble

[smokiibear]

I am using the following code to conduct a filesearch and write the files
found to an array:

k = 0
For i = 1 To UBound(myFolders)
With Application.FileSearch
.NewSearch
.LookIn = myFolders(i)
.SearchSubFolders = False
.FileName = "*.csv"
If .Execute() > 0 Then
ReDim Preserve filelist(1 To k + .FoundFiles.COUNT)
For j = 1 To .FoundFiles.COUNT
k = k + 1
filelist(k) = .FoundFiles(j)
Next j
Else
MsgBox "There were no files found."
End If
End With
Next i

The code works well only the first time run after restarting excel.
However, if I have already run the code, then add files to any previously
selected paths, the code only sees the files that were there during the
first execution of the code?

Any ideas?

Smokii

 

[Guest]

Hello,

The following works for me:

Sub FilTime()
Dim myFolders As Variant, i As Long, j As Long
Dim fileList() As String
Let myFolders = [{"c:\temp","p:\data"}]
With Application.FileSearch
.NewSearch
'Create string: "c:\temp;p:\data"
.LookIn = Join$(myFolders, ";")
.SearchSubFolders = False
.Filename = "*.csv"
If .Execute() > 0 Then
ReDim Preserve fileList(1 To .FoundFiles.Count)
For j = 1 To UBound(fileList)
fileList(j) = .FoundFiles(j)
Next j
Else: MsgBox "There were no files found."
End If
End With
If j Then
For i = LBound(fileList) To UBound(fileList)
Debug.Print fileList(i)
Next
Debug.Print UBound(fileList)
End If
End Sub

You could use join() (if you have xl 2000+) to convert your array to a
string to search on if you must start with an array.

Regards,
Nate Oliver

 

[Guest]

Sorry, no need for Redim Preserve, try:

Sub FilTime2()
Dim myFolders As Variant, i As Long, j As Long
Dim fileList() As String
Let myFolders = [{"c:\temp","p:\data"}]
With Application.FileSearch
.NewSearch
'Create string: "c:\temp;p:\data"
.LookIn = Join$(myFolders, ";")
.SearchSubFolders = False
.Filename = "*.csv"
If .Execute() > 0 Then
ReDim fileList(1 To .FoundFiles.Count)
For j = 1 To UBound(fileList)
fileList(j) = .FoundFiles(j)
Next j
Else: MsgBox "There were no files found."
End If
End With
If j Then
For i = LBound(fileList) To UBound(fileList)
Debug.Print fileList(i)
Next
Debug.Print UBound(fileList)
End If
End Sub

Regards,
Nate Oliver

 

[smokiibear]

i also have a intermitant problem when the .execute > 0 is executing. On
ocassion, I must wait 2 minutes for it to search for one file. Any ideas
on this?

Thanks for all your help.

Smokii

 

[Guest]

Hard to say based on your post. It can be very slow on a slow network drive...

Care to provide your code and situation?

Also, the following may be of interest
http://msdn.microsoft.com/library/en-us/modcore/html/deovrthebasicsoffilesearching.asp

Regards, NPO

 

[smokiibear]

Here is my current code....it works fast, BUT doesn't get all the files
matching the .filename = "*.csv"...I'm not clear why??? In some folders,
it only gives me 1 of 8 available files, in others it gives me 2 of 3,
and in others, in gives me all the files that match the .filename =
"*.csv":

With Application.FileSearch
.RefreshScopes
.NewSearch
.LookIn = spath
.SearchSubFolders = False
.filename = "*.csv"

'Checks to see if there are any files and provides error handling if
not
If .Execute() > 0 Then

ReDim filesavailable(1 To k + .FoundFiles.COUNT)
For j = 1 To .FoundFiles.COUNT
filesavailable(j) = .FoundFiles(j)
Next j

'Displays avaialbe files in a list box and turns on multiple
selection for avaiable list box
With lbFilesAvailable
.MultiSelect = fmMultiSelectMulti
.List = filesavailable
End With

'turns on multiple selection for list box of selected files
lbFilesSelected.MultiSelect = fmMultiSelectMulti
Else
MsgBox ("No files found. Please BROWSE another directory.")
End If

End With


Smokii

 

[Dave Peterson]

xl2002? (or xl2003 maybe)?

There seems to be some kind of bug in .filesearch that give wrong results in
xl2002.

Maybe you could use Dir:

Option Explicit
Sub testme()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.CSV")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
'do your stuff
Next fCtr
End If

End Sub

Here is my current code....it works fast, BUT doesn't get all the files
matching the .filename = "*.csv"...I'm not clear why??? In some folders,
it only gives me 1 of 8 available files, in others it gives me 2 of 3,
and in others, in gives me all the files that match the .filename =
"*.csv":

With Application.FileSearch
.RefreshScopes
.NewSearch
.LookIn = spath
.SearchSubFolders = False
.filename = "*.csv"

'Checks to see if there are any files and provides error handling if
not
If .Execute() > 0 Then

ReDim filesavailable(1 To k + .FoundFiles.COUNT)
For j = 1 To .FoundFiles.COUNT
filesavailable(j) = .FoundFiles(j)
Next j

'Displays avaialbe files in a list box and turns on multiple
selection for avaiable list box
With lbFilesAvailable
.MultiSelect = fmMultiSelectMulti
.List = filesavailable
End With

'turns on multiple selection for list box of selected files
lbFilesSelected.MultiSelect = fmMultiSelectMulti
Else
MsgBox ("No files found. Please BROWSE another directory.")
End If

End With

Smokii

Hello,

The following works for me:

Sub FilTime()
Dim myFolders As Variant, i As Long, j As Long
Dim fileList() As String
Let myFolders = [{"c:\temp","p:\data"}]
With Application.FileSearch
.NewSearch
'Create string: "c:\temp;p:\data"
.LookIn = Join$(myFolders, ";")
.SearchSubFolders = False
.Filename = "*.csv"
If .Execute() > 0 Then
ReDim Preserve fileList(1 To .FoundFiles.Count)
For j = 1 To UBound(fileList)
fileList(j) = .FoundFiles(j)
Next j
Else: MsgBox "There were no files found."
End If
End With
If j Then
For i = LBound(fileList) To UBound(fileList)
Debug.Print fileList(i)
Next
Debug.Print UBound(fileList)
End If
End Sub

You could use join() (if you have xl 2000+) to convert your array to a
string to search on if you must start with an array.

Regards,
Nate Oliver

 

[smokiibear]

Yep...using xl2002. The Dir function works great.

Thanks Dave.

Smokii