I didn't know whether to include this with my other post, because it came up in the same program, but is really a different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these messages? Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows program, so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps it will be OK on my own PC!

Thanks and regards
--
Alison

Hi Ali,

Try this,

'---------------------------
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.Filename = strLocFile & "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
y = .FoundFiles.Count
End With
'---------------------------


Salutations!


"Ali Campbell" a écrit dans le message de
...
I didn't know whether to include this with my other post, because it came up in the same program, but is really a
different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no
extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even
though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method
of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these messages?
Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows program,
so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps
it will be OK on my own PC!

Thanks and regards
--
Alison

Take one more look at VBA's help for .matchtextexactly:

MatchTextExactly Property

True if the specified file search will find only files whose body text or file
properties contain the exact word or phrase that you've specified. Read/write
Boolean.

==
It doesn't refer to the name of the file--it's stuff in the file or in the
properties for that file.


Ali Campbell wrote:

I didn't know whether to include this with my other post, because it came up in the same program, but is really a different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these messages? Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows program, so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps it will be OK on my own PC!

Thanks and regards
--
Alison

--

Dave Peterson

Hi Michdenis -

I had already tried .FileType, but left the extension at *.xls, so will try with *.*; I'm also not sure if the change of order between .LookIn and .FileName is significant, so will try that as well.

I'm off to the client later to test, so will report back after that.

Thanks

Alison
--
Alison


"michdenis" wrote:

Hi Ali,

Try this,

'---------------------------
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.Filename = strLocFile & "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
y = .FoundFiles.Count
End With
'---------------------------


Salutations!


"Ali Campbell" a écrit dans le message de
...
I didn't know whether to include this with my other post, because it came up in the same program, but is really a
different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no
extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even
though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method
of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these messages?
Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows program,
so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps
it will be OK on my own PC!

Thanks and regards
--
Alison

Hi Ali,

In fact, this is the combinaison of these two lines that will give you the resultat you want !
I know, it's not obvious, but it's working !

..Filename = strLocFile & "*.*"
..FileType = msoFileTypeExcelWorkbooks


Salutations!





"Ali Campbell" a écrit dans le message de
...
Hi Michdenis -

I had already tried .FileType, but left the extension at *.xls, so will try with *.*; I'm also not sure if the change of
order between .LookIn and .FileName is significant, so will try that as well.

I'm off to the client later to test, so will report back after that.

Thanks

Alison
--
Alison


"michdenis" wrote:

Hi Ali,

Try this,

'---------------------------
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.Filename = strLocFile & "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
y = .FoundFiles.Count
End With
'---------------------------


Salutations!


"Ali Campbell" a écrit dans le message de
...
I didn't know whether to include this with my other post, because it came up in the same program, but is really a
different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no
extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even
though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method
of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these
messages?
Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows
program,
so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps
it will be OK on my own PC!

Thanks and regards
--
Alison

Thanks - I'm just off to try it now.

Cheers
--
Alison


"michdenis" wrote:

Hi Ali,

In fact, this is the combinaison of these two lines that will give you the resultat you want !
I know, it's not obvious, but it's working !

..Filename = strLocFile & "*.*"
..FileType = msoFileTypeExcelWorkbooks


Salutations!





"Ali Campbell" a écrit dans le message de
...
Hi Michdenis -

I had already tried .FileType, but left the extension at *.xls, so will try with *.*; I'm also not sure if the change of
order between .LookIn and .FileName is significant, so will try that as well.

I'm off to the client later to test, so will report back after that.

Thanks

Alison
--
Alison


"michdenis" wrote:

Hi Ali,

Try this,

'---------------------------
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.Filename = strLocFile & "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
y = .FoundFiles.Count
End With
'---------------------------


Salutations!


"Ali Campbell" a écrit dans le message de
...
I didn't know whether to include this with my other post, because it came up in the same program, but is really a
different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no
extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even
though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method
of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these
messages?
Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows
program,
so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps
it will be OK on my own PC!

Thanks and regards
--
Alison

Hi Dave -

I had noted that wording, but since File Name appears as a property both in Windows Explorer and Word's File Open Find tool I made the assumption that the term "property" encompassed the file name. Obviously not!

Thanks
--
Alison


"Dave Peterson" wrote:

Take one more look at VBA's help for .matchtextexactly:

MatchTextExactly Property

True if the specified file search will find only files whose body text or file
properties contain the exact word or phrase that you've specified. Read/write
Boolean.

==
It doesn't refer to the name of the file--it's stuff in the file or in the
properties for that file.


Ali Campbell wrote:

I didn't know whether to include this with my other post, because it came up in the same program, but is really a different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these messages? Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows program, so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps it will be OK on my own PC!

Thanks and regards
--
Alison

--

Dave Peterson

Sorry, Michdenis - This still doesn't work on the Excel 97/Windows 98 combination, so I had to leave my workaround in place. They had upgraded the other 2 PCs to Excel 2000/Windows 2000, and that worked OK, but not the older configuration.

Thanks again for your help.

Cheers
--
Alison


"michdenis" wrote:

Hi Ali,

In fact, this is the combinaison of these two lines that will give you the resultat you want !
I know, it's not obvious, but it's working !

..Filename = strLocFile & "*.*"
..FileType = msoFileTypeExcelWorkbooks


Salutations!





"Ali Campbell" a écrit dans le message de
...
Hi Michdenis -

I had already tried .FileType, but left the extension at *.xls, so will try with *.*; I'm also not sure if the change of
order between .LookIn and .FileName is significant, so will try that as well.

I'm off to the client later to test, so will report back after that.

Thanks

Alison
--
Alison


"michdenis" wrote:

Hi Ali,

Try this,

'---------------------------
With Application.FileSearch
.NewSearch
.LookIn = strFolderName
.Filename = strLocFile & "*.*"
.FileType = msoFileTypeExcelWorkbooks
.Execute
y = .FoundFiles.Count
End With
'---------------------------


Salutations!


"Ali Campbell" a écrit dans le message de
...
I didn't know whether to include this with my other post, because it came up in the same program, but is really a
different issue.

When I use the following code in Excel 97 (regardless of operating system) (to determine whether a file with no
extension - strLocFile - from one folder has a matching file with an .xls extension in another and, if so, count it):

With Application.FileSearch
.NewSearch
.FileName = strLocFile & ".xls"
.LookIn = strFolderName
.MatchTextExactly = True 'This doesn't work as required
.Execute
y = .FoundFiles.Count
End With

where strLocFile is a numeric file name (eg 1234), the procedure will consider the file 12345.xls to be a match, even
though I've used .MatchTextExactly.

I wrote the following workaround, which is placed immediately before the End With:

'This replaces MatchTextExactly property
' by testing for exact string match
If y 0 Then
For i = 1 To y
If LCase(Trim(strLocFile & ".xls")) = _
ParentFolder(LCase(Trim(.FoundFiles(1)))) Then
z = z + 1
Exit For
End If
Next i
End If

(ParentFolder is a function I wrote to get the last folder or file in a path; I'm now aware of another, simpler method
of getting the file name only, but haven't had a chance to test/apply it yet.)

Am I misinterpreting the use of .MatchTextExactly, or is it a bug? Does anyone have a tidier workaround?

BTW, does anyone else have a problem with the scroll bar to the right of the message window when posting these
messages?
Neither the arrow keys nor the grey scroll bar areas work (on this PC, at least) as they do in a normal windows
program,
so I can only use the scroll button or the keyboard arrows; however, I haven't posted in this forum before, so perhaps
it will be OK on my own PC!

Thanks and regards
--
Alison