FileSearch problems

[Henryk Birecki]

I do not seem to be able to make FileSearch macro to work properly. It
seems that it works on some installations and not at all on others,
and even when it "works" I have to go through hoops to make it work.
Purpose of the macro is to open a file as a worksheet. File is of a
text type, but is given a "strange" name, though if the name is not
"strange" the macro does not behave any better. The file name I am
looking for is of the form xxxxx.yyyyyyyy.zzz.20050210 Yes, the end
is a date. Only the dates are different for different files. Macro
code is below. This used to work just fine in older versions of Excel
on Win2000. But with Office 2003 Excel and WindowsXP I have nothing
but grief. Failure is in two modes 1) file is not found (but it
certainly is there- one or more) and the message box appears
immediately to say so, 2) Excel starts grinding the disk forever and
has to be terminated by force.

Any suggestions as to what is going on?

Henryk Birecki

'
' TryOpeningData Macro
' Macro recorded 9/8/2001 by Henryk Birecki
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Dim k, i, num, f$
a$ = CurDir
d$ = "C:\Documents and Settings\birecki\My Documents\My
Webs\mywebsite\restricted\logs"
ChDir d$
Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = d$
.Filename = "xxx.yyy*.20*" ' this is a hacked name to make the
' macro work in some cases note that it has
' only 2 dots instead of three
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByLastModified,
SortOrder:=msoSortOrderAscending) > 0 Then
num = .FoundFiles.Count
For i = .FoundFiles.Count To 1 Step -1
f$ = .FoundFiles(i)
k = MsgBox(f$, vbYesNoCancel)
If vbCancel = k Then Exit For
If vbYes = k Then
Workbooks.OpenText Filename:= _
f$ _
, Origin:=xlWindows, StartRow:=1,
DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=True, Semicolon:=False _
, Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1))
End If
Next i
Else
MsgBox "There were no files found."
End If
End With

 

[Dave Peterson]

If you search the *excel* newsgroups via google for "flakey filesearch", you'll
find lots of hits.

You may want to revert to using Dir() to return the list of files--and check for
your filename pattern.

Option Explicit
Sub testme03()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.*")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
If myFile Like "?????.????????.???.########" Then
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
End If
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
'do your work here
MsgBox myPath & myFiles(fCtr)
Next fCtr
End If

End Sub