Searching for a Partial Filename
 

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?

Searching for a Partial Filename
 

Mark,

Try

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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?

Searching for a Partial Filename
 

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 wrote:
Mark,

Try

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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?

Searching for a Partial Filename
 

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

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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 wrote:
Mark,

Try

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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?

Searching for a Partial Filename
 

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.

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

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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 wrote:
Mark,

Try

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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?