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

Allan,

You can have a variation of Jim's code without library references

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.File
Dim objFolder As Object
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 = CreateObject("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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"alondon" wrote in message ...
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