Using Filesearch - in reverse. 1

[Nitibob]

Hi all,

I am using the filesearch option to find and then open a series of Excel documents.

My code is:

Set fs = Application.FileSearch

With fs
.LookIn = "C:\Mike"
.FileName = Not "*mo*"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Workbooks.Open .FoundFiles(i)

Next i

Else
MsgBox "There were no files found."
End If
End With


Now, I want to find and open all the files that have a name that isn't "*Mo*". Any Ideas?

 

[Nitibob]

Sorry, I should have said the 'Not' statement doesn't work

 

[RobBroekhuis]

I think you're going to have to search for all files, and use string functions to determine for yourself if they are *Mo*, i.e.

if instr(ucase(.FoundFiles.Item(i)),"MO&quot=0 then
...
end if

Rob

 

[xlbo]

Can you not use:
.FileName <> &quot;*mo*&quot;
Rgds
~Geoff~

 

[RobBroekhuis]

How would that work, Geoff? FileName is a simple property, which can have a value assigned to it use the &quot;=&quot; operator - but not, as far as I know, an un-value using a &quot;<>&quot; operator. You've got me curious now.
Rob

 

[Nitibob]

Thank you both for your quick responses.

Rob: that code worked excellently. Just what I needed!

the code now is:

With fs
.LookIn = &quot;C:\Mike&quot;
.FileName = &quot;*&quot;
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
MsgBox &quot;There were &quot; & .FoundFiles.Count & _
&quot; file(s) found.&quot;
For i = 1 To .FoundFiles.Count
If InStr(UCase(.FoundFiles(i)), &quot;MO&quot = 0 Then
Workbooks.Open .FoundFiles(i)
End If

Next i

Else
MsgBox &quot;There were no files found.&quot;
End If
End With

 

[xlbo]

Rob - correct - it was just an idea - tried it and VBA doesn't like it Rgds
~Geoff~