Thank You Group Application.FileSearch not working? 1/16/2005 10:59PM
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
|