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





The Original Message

Sent: 1/16/2005 10:59 PM



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