Application.FileSearch is not working??

alondon · 01-17-2005, 03:06 AM

Folks,

The routine below loads all the file names in the "DataDirectory" into a
ComboBox where the first 4 letters of the file name are the same as the
"SerialNumber". It works perfectly on some XP SP2 machines, but leaves out
files reciently added to the "DataDirectory" on other XP SP2 machines?
Always works on mapped drives, but has problems on local drives. Restart or
running Explore and hitting F5 a few times seems to solve the problem.

It looks like XP will buffer directories, but will not always update them
properly. Is something wrong with my code or is the Application.FileSearch
method just flakey? Is there a way to get around this problem?

I really need this code, or something that accomplishes the same thing, to
work Any Ideas?

Thanks,

Allan P. London, CPA
San Francisco, CA

______________________________________________________________
Private Sub UserForm_Initialize()

'Load scenario file names into ComboBox1

Dim fs As FileSearch

Dim I As Integer

On Error GoTo ErrorCode

Set fs = Application.FileSearch

With fs

.RefreshScopes

.NewSearch

.LookIn = DataDirectory

.Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"

If .Execute(SortBy:=msoSortByFileName, _

SortOrder:=msoSortOrderAscending) > 0 Then

'MsgBox .FoundFiles.Count & " file(s) found."

For I = 1 To .FoundFiles.count

ComboBox1.AddItem UCase(.FoundFiles(I))

Next I

Else

MsgBox "There were no files found for " & CompanyName & _

" on the " & DataDirectory & "Directory"

GoTo ErrorCode

End If

End With

Exit Sub

ErrorCode:

Retval = False

Unload RecallScenario

End Sub

Norman Jones · 01-17-2005, 04:39 AM

Hi Alondon,

There have been munerous posts suggesting that Filesearch , at least in its
xl2002 implementation is very flakey an I am aware that many have dropped it
from use.

See for example:

http://tinyurl.com/6p6vl

and

http://tinyurl.com/6cen2

---
Regards,
Norman

"alondon" <[email protected]> wrote in message
news:[email protected]...
> Folks,
>
> The routine below loads all the file names in the "DataDirectory" into a
> ComboBox where the first 4 letters of the file name are the same as the
> "SerialNumber". It works perfectly on some XP SP2 machines, but leaves
> out files reciently added to the "DataDirectory" on other XP SP2 machines?
> Always works on mapped drives, but has problems on local drives. Restart
> or running Explore and hitting F5 a few times seems to solve the problem.
>
> It looks like XP will buffer directories, but will not always update them
> properly. Is something wrong with my code or is the
> Application.FileSearch method just flakey? Is there a way to get around
> this problem?
>
> I really need this code, or something that accomplishes the same thing, to
> work Any Ideas?
>
> Thanks,
>
> Allan P. London, CPA
> San Francisco, CA
>
> ______________________________________________________________
> Private Sub UserForm_Initialize()
>
> 'Load scenario file names into ComboBox1
>
>
>
> Dim fs As FileSearch
>
> Dim I As Integer
>
>
>
> On Error GoTo ErrorCode
>
> Set fs = Application.FileSearch
>
>
>
> With fs
>
> .RefreshScopes
>
> .NewSearch
>
> .LookIn = DataDirectory
>
> .Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"
>
> If .Execute(SortBy:=msoSortByFileName, _
>
> SortOrder:=msoSortOrderAscending) > 0 Then
>
> 'MsgBox .FoundFiles.Count & " file(s) found."
>
> For I = 1 To .FoundFiles.count
>
> ComboBox1.AddItem UCase(.FoundFiles(I))
>
> Next I
>
> Else
>
> MsgBox "There were no files found for " & CompanyName & _
>
> " on the " & DataDirectory & "Directory"
>
> GoTo ErrorCode
>
> End If
>
> End With
>
> Exit Sub
>
>
>
> ErrorCode:
>
> Retval = False
>
> Unload RecallScenario
>
> End Sub
>
>

Norman Jones · 01-17-2005, 04:39 AM

Hi Alondon,

Looking at your code, try changing the line:

> .Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"

to:

.Filename = Mid(Range("theSerialNumber"), 1, 4) & ".xls"

---
Regards,
Norman

"alondon" <[email protected]> wrote in message
news:[email protected]...
> Folks,
>
> The routine below loads all the file names in the "DataDirectory" into a
> ComboBox where the first 4 letters of the file name are the same as the
> "SerialNumber". It works perfectly on some XP SP2 machines, but leaves
> out files reciently added to the "DataDirectory" on other XP SP2 machines?
> Always works on mapped drives, but has problems on local drives. Restart
> or running Explore and hitting F5 a few times seems to solve the problem.
>
> It looks like XP will buffer directories, but will not always update them
> properly. Is something wrong with my code or is the
> Application.FileSearch method just flakey? Is there a way to get around
> this problem?
>
> I really need this code, or something that accomplishes the same thing, to
> work Any Ideas?
>
> Thanks,
>
> Allan P. London, CPA
> San Francisco, CA
>
> ______________________________________________________________
> Private Sub UserForm_Initialize()
>
> 'Load scenario file names into ComboBox1
>
>
>
> Dim fs As FileSearch
>
> Dim I As Integer
>
>
>
> On Error GoTo ErrorCode
>
> Set fs = Application.FileSearch
>
>
>
> With fs
>
> .RefreshScopes
>
> .NewSearch
>
> .LookIn = DataDirectory
>
> .Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"
>
> If .Execute(SortBy:=msoSortByFileName, _
>
> SortOrder:=msoSortOrderAscending) > 0 Then
>
> 'MsgBox .FoundFiles.Count & " file(s) found."
>
> For I = 1 To .FoundFiles.count
>
> ComboBox1.AddItem UCase(.FoundFiles(I))
>
> Next I
>
> Else
>
> MsgBox "There were no files found for " & CompanyName & _
>
> " on the " & DataDirectory & "Directory"
>
> GoTo ErrorCode
>
> End If
>
> End With
>
> Exit Sub
>
>
>
> ErrorCode:
>
> Retval = False
>
> Unload RecallScenario
>
> End Sub
>
>

Patrick Molloy · 01-17-2005, 04:39 AM

You could as easily use th eDIR() function.

DIM fn as string
dim spec as string

spec = LEFT(Range("theSerialNumber").Value, 4) & "*.xls"

fn = DIR(spec)
DO WHILE fn <> ""
ComboBox1.AddItem
fn = DIR()
LOOP

"alondon" wrote:

> Folks,
>
> The routine below loads all the file names in the "DataDirectory" into a
> ComboBox where the first 4 letters of the file name are the same as the
> "SerialNumber". It works perfectly on some XP SP2 machines, but leaves out
> files reciently added to the "DataDirectory" on other XP SP2 machines?
> Always works on mapped drives, but has problems on local drives. Restart or
> running Explore and hitting F5 a few times seems to solve the problem.
>
> It looks like XP will buffer directories, but will not always update them
> properly. Is something wrong with my code or is the Application.FileSearch
> method just flakey? Is there a way to get around this problem?
>
> I really need this code, or something that accomplishes the same thing, to
> work Any Ideas?
>
> Thanks,
>
> Allan P. London, CPA
> San Francisco, CA
>
> ______________________________________________________________
> Private Sub UserForm_Initialize()
>
> 'Load scenario file names into ComboBox1
>
>
>
> Dim fs As FileSearch
>
> Dim I As Integer
>
>
>
> On Error GoTo ErrorCode
>
> Set fs = Application.FileSearch
>
>
>
> With fs
>
> .RefreshScopes
>
> .NewSearch
>
> .LookIn = DataDirectory
>
> .Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"
>
> If .Execute(SortBy:=msoSortByFileName, _
>
> SortOrder:=msoSortOrderAscending) > 0 Then
>
> 'MsgBox .FoundFiles.Count & " file(s) found."
>
> For I = 1 To .FoundFiles.count
>
> ComboBox1.AddItem UCase(.FoundFiles(I))
>
> Next I
>
> Else
>
> MsgBox "There were no files found for " & CompanyName & _
>
> " on the " & DataDirectory & "Directory"
>
> GoTo ErrorCode
>
> End If
>
> End With
>
> Exit Sub
>
>
>
> ErrorCode:
>
> Retval = False
>
> Unload RecallScenario
>
> End Sub
>
>
>

Jim Cone · 01-17-2005, 02:06 PM

Allan,

As an alternative approach (the other suggestions will probably work)...

FileScriptingObject code is included with all recent versions of Windows.
It can be freely mixed in with VBA code.
It is much more reliable than the FileSearch code in VBA (in my experience).
The following code worked for me. The variables were changed so the code
could be tested on my machine.
With some minor changes it could work for you.

'-----------------------------------------
Sub UserForm_Initialize()
'Requires project reference to "Microsoft Scripting Runtime"
'(scrrun.dll)
'Jim Cone - January 17, 2005
On Error GoTo ErrorCode
Dim objFso As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strDataDirectory As String
Dim strSerialNumber As String
Dim blnFound As Boolean

strSerialNumber = "####"
strDataDirectory = "C:\Documents and Settings\user\My Documents\Excel Files"
Set objFso = New Scripting.FileSystemObject
Set objFolder = objFso.GetFolder(strDataDirectory)

For Each objFile In objFolder.Files
If objFile.Name Like strSerialNumber & "*.xls" Then
ComboBox1.AddItem UCase(objFile.Name)
blnFound = True
End If
Next 'objFile

If Not blnFound Then
MsgBox "There were no files found for CompanyName" & vbCr & _
" on the " & strDataDirectory & " Directory"
GoTo ErrorCode
End If
Set objFile = Nothing
Set objFolder = Nothing
Set objFso = Nothing
Exit Sub

ErrorCode:
'do stuff
End Sub
'---------------------------------------------

Regards,
Jim Cone
San Francisco, USA

"alondon" <[email protected]> wrote in message
news:[email protected]...
> Folks,
>
> The routine below loads all the file names in the "DataDirectory" into a
> ComboBox where the first 4 letters of the file name are the same as the
> "SerialNumber". It works perfectly on some XP SP2 machines, but leaves out
> files reciently added to the "DataDirectory" on other XP SP2 machines?
> Always works on mapped drives, but has problems on local drives. Restart or
> running Explore and hitting F5 a few times seems to solve the problem.
> It looks like XP will buffer directories, but will not always update them
> properly. Is something wrong with my code or is the Application.FileSearch
> method just flakey? Is there a way to get around this problem?
> I really need this code, or something that accomplishes the same thing, to
> work Any Ideas?
> Thanks,
> Allan P. London, CPA
> San Francisco, CA
________________________________________________________
> Private Sub UserForm_Initialize()
> 'Load scenario file names into ComboBox1
> Dim fs As FileSearch
> Dim I As Integer
> On Error GoTo ErrorCode
> Set fs = Application.FileSearch
> With fs
> .RefreshScopes
> .NewSearch
> .LookIn = DataDirectory
> .Filename = Mid(Range("theSerialNumber"), 1, 4) & "*.xls"
> If .Execute(SortBy:=msoSortByFileName, _
> SortOrder:=msoSortOrderAscending) > 0 Then
> 'MsgBox .FoundFiles.Count & " file(s) found."
> For I = 1 To .FoundFiles.count
> ComboBox1.AddItem UCase(.FoundFiles(I))
> Next I
> Else
> MsgBox "There were no files found for " & CompanyName & _
> " on the " & DataDirectory & "Directory"
> GoTo ErrorCode
> End If
> End With
> Exit Sub
> ErrorCode:
> Retval = False
> Unload RecallScenario
> End Sub

alondon · 01-18-2005, 12:06 AM

Folks,

Thank you all for your help.

Norman convinced me that I needed an alternative to the "flakey" FileSearch
method. I finally settled on Patrick Malloy's solution as the most direct
and simplest (see below). However, I learned a lot by reviewing Jim's
elegant scripting solution; unfortunately my winery solutions go to a lot of
people that would never be able to properly reference a type library.

Wow, you folks always come through so I can continue to promise folks
solutions that I could not possibly deliver without your help

Cheers,

Allan P. London, CPA

____________________________________________________________
THE SOLUTION:

Private Sub UserForm_Initialize()

'Load scenario file names into ComboBox1

'Complements of Patrick Molloy, Excel Programming User Group

Dim FoundFile As Boolean

Dim fn As String

Dim Spec As String

Dim I As Integer

On Error GoTo ErrorCode

'Initialize

ChDir DataDirectory

FoundFile = False

Spec = Left(Range("theSerialNumber").Value, 4) & "*.xls"

fn = Dir(Spec)

'Load Combo Box

Do While fn <> ""

FoundFile = True

ComboBox1.AddItem fn

'Iterate to next qualified file name

fn = Dir()

Loop

'Cleanup

ChDir theSystemLocation

If Not FoundFile Then GoTo ErrorCode

Exit Sub

ErrorCode:

MsgBox "There were no files found for " & CompanyName & _

" on the " & DataDirectory & "Directory"

Retval = False

Unload RecallScenario

End Sub