Searching for a Partial Filename

[mark.diffley]

I am trying create a macro to open a workbook on a known network drive.
The subfolders that hold the workbook are based on the 5 pieces of
information in the workbook when it was initially saved. I want to be
able to find the file based on just one unique part (workorder) of the
filename. I tried the FileSearch example to see what would happen but
seem to keep getting an empty list back (the .execute > 0 returns
false). Here's the code I used:

Set fs = Application.filesearch
With fs
.LookIn = "\\d1fs02\enginsp$\TEMP\"
.filename = workorder
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Where workorder is based on the value from a textbox on a form. I'm
running Excel 2K on Win 2K. Any suggestions or help?

 

[Bernie Deitrick]

Mark,

Try

.filename = "*" & workorder & "*"

HTH,
Bernie
MS Excel MVP

 

[mark.diffley]

Still nothing. I know the start of every file that this will ever
search for is "!WO# " and then the number itself. The workorder
variable is a string with "!WO# XXXXXXX" where XXXXXX is the individual
work order number.


Mark

 

[Bernie Deitrick]

You really need to post the code, especially the part that you are using to generate the workorder
number....

HTH,
Bernie
MS Excel MVP

 

[mark.diffley]

I posted the code earlier, but here it is again this time including how
I define the workorder variable:

workorder = "*!WO# " & workorderbox.Value & "*.xls"
Set fs = Application.filesearch
With fs
.LookIn = "\\d1fs02\enginsp$\TEMP\"
.filename = workorder
.MatchTextExactly = False
.FileType = msoFileTypeExcelWorkbooks
'If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
'Else
MsgBox "There were no files found."
'End If
End With


The variable will end up as "!WO# XXXXX*.xls" with XXXXX as the number
that is input into the textbox.