Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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...esearching.asp Regards, NPO "smokiibear" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 "?B?TmF0ZSBPbGl2ZXI=?=" wrote in : 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
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 smokiibear wrote: 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 "?B?TmF0ZSBPbGl2ZXI=?=" wrote in : 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
.filesearch trouble
Yep...using xl2002. The Dir function works great.
Thanks Dave. Smokii Dave Peterson wrote in : xl2002? (or xl2003 maybe)? There seems to be some kind of bug in .filesearch that give wrong results in xl2002. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filesearch in VB6 | Excel Programming | |||
FileSearch | Excel Programming | |||
FileSearch using VBA | Excel Programming | |||
.FileSearch | Excel Programming | |||
FileSearch Problem | Excel Programming |