FileSearch Object. Strange result with Wildcards

[Alan Carpenter]

Access 8 on Win98 and WinXP

I'm having trouble with wildcards in the .Filename property of the
FileSearch Object giving different results on win98 and XP. I've been
successfully using FileSearch in win98 to fill an array with filenames with
no problems since about 1998.

From the 97 Help:
(The Filename Property of the FileSearch object)
--------------------------
Returns or sets the name of the file to look for during a file search. The
name of the file may include the * (asterisk) or ? (question mark)
wildcards. Use the question mark wildcard to match any single character.
For example, type gr?y to match both "gray" and "grey." Use the asterisk
wildcard to match any number of characters. For example, type *.txt to find
all files that have the .TXT extension. Read-write String.
----------------------------

Now that's fairly well spelled out, just what I want and just what I
expected.
It's what I get on my machine both from Access and Excel., *with or
without* a Reference to MSO97.DLL Without the Reference I can't use the
constants such as msoFileTypeAllFiles, but with the Reference I can find
that msoFileTypeAllFiles = 1 and use 1. That's the only constant I need,
and with "All Files" the next step in the selection path should be the
..Filename wildcard. The .Execute defaults for (SortBy, SortOrder,
AlwaysAccurate) are what I want, though I've noticed that although Help
says SortBy " ... can be either of the following MsoSortOrder constants:
msoSortOrderAscending or msoSortOrderDescending," when I omit SortBy I get
unsorted results. That's fine.

However, when my friend Kirk in another city runs it on his machine using
Office 8 under XP, he gets different results. A filespec of "*.doc;*.txt"
returns nothing, although there are files which match those specs.
Searching from File - Open Database - Advanced using those specs works
perfectly. (The Help indicates the FileSearch Object is used for that
search.)

I'm at a loss. Kirk has set the MSO97.DLL Reference and moved it as far up
the Reference list as it will stick.

Our "benchmark" is a search for a single asterisk. I return all files, he,
alas, gets nothing.

For what it's worth I'll paste my rather butchered test code.
ShowMe should work, but testgFindFiles() will need a valid path.

Can anyone tell my why a spec of "*.doc;*.txt" or a spec of "*" would
return nothing on an XP machine?

Cheers,
Alan
'################
Option Compare Database
Option Explicit
Public gFoundFiles As Variant

Function gFindFiles(findWhere, findWhat, _
Optional DoSubFolders As Boolean = True) As Variant
'Reference to Office C:\OFFICE97\OFFICE\MSO97.DLL

'findWhat = fixFindWhat(findWhat) ' For Kirk
Dim aFiles() As Variant
'Dim msg ' for Debug.
Dim I
'With Office.FileSearch
With Application.FileSearch
.NewSearch
'.LookIn = "n:\aa\" 'Force Backslash?Application.
If .LookIn = "C:\My Documents" Then Stop
'When findWhere doesn't exist, default is "My Documents"
'Maybe If Dir ... before "With ..
.LookIn = findWhere
.SearchSubFolders = DoSubFolders
.MatchAllWordForms = False
.MatchTextExactly = False
.FileType = 1 'msoFileTypeAllFiles
.Filename = findWhat '"*.doc;*.txt"
.Execute 'msoSortBy... 'RetVal could be useful.
ReDim aFiles(.FoundFiles.Count)
For I = 1 To .FoundFiles.Count
'msg = msg & .FoundFiles(I) & vbCrLf
aFiles(I) = .FoundFiles(I)
Next I
End With
' AlanPrint msg
gFindFiles = aFiles
'gFoundFiles = aFiles
Erase aFiles
End Function
Function fixFindWhat(findWhat)
Select Case findWhat
Case IsNull(findWhat), "*", "*.*"
fixFindWhat = ""
Case Else
fixFindWhat = findWhat
End Select
End Function

Sub testgFindFiles()
Dim Gottem
Dim thePath
'thePath = "I:\"
thePath = "H:\CDList\" 'Kirk, use whatever works.
Gottem = gFindFiles(thePath, "*.mp3")
Debug.Print UBound(Gottem), "*.mp3"
Gottem = gFindFiles(thePath, "*.wav")
Debug.Print UBound(Gottem), "*.wav"
Gottem = gFindFiles(thePath, "*.mp3;*.wav")
Debug.Print UBound(Gottem), "*.mp3;*.wav"
Gottem = gFindFiles(thePath, "*.*")
Debug.Print UBound(Gottem), "*.*"
Gottem = gFindFiles(thePath, "*")
Debug.Print UBound(Gottem), "*"
Gottem = gFindFiles(thePath, "")
Debug.Print UBound(Gottem), ""
Debug.Print "Done"
Erase Gottem
End Sub

Sub ShowMe()
Dim Gottem, msg
Dim thePath$, I
thePath = "c:\windows\"
Gottem = gFindFiles(thePath, "*.txt;*.ini", False)
For I = 1 To UBound(Gottem)
msg = msg & Gottem(I) & vbCrLf
Next
AlanPrint msg
End Sub
Sub AlanPrint(msg)
Dim PrintPlace$
PrintPlace$ = Environ("temp") & "\AlanTemp.txt"
Dim FileNum As Integer
FileNum = FreeFile
Open PrintPlace$ For Output As #FileNum
Print #FileNum, msg
Close FileNum
PrintPlace$ = "notepad.exe " & PrintPlace$
msg = Shell(PrintPlace$, 1) 'shrink msg variant
End Sub
'################

[Lyle Fairfield]

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

Access 8 on Win98 and WinXP

I'm having trouble with wildcards in the .Filename property of the
FileSearch Object giving different results on win98 and XP. I've been
successfully using FileSearch in win98 to fill an array with filenames
with no problems since about 1998.

Many years ago (1999) we found that FileSearch did not succeed when it
came upon files that were not really files but shortcuts such a ".lnk"
files and ".pif" files. At that time FileSearch might error, but more
commonly it stopped searching, and "reported" that there were no more
files matching the pattern.

At that time I wrote my own FindFiles UDF (below).
I expect that now there are other better, simpler solutions and that
someone will suggest one.

And, of course, the error I describe may have been corrected long ago.

I have not tested this code in any modern version of Access and obviously
it does not reflect my current coding style.

Sub FindFiles()
Dim afiles As Variant
Dim varfile As Variant
Dim booStatusBar As Boolean
booStatusBar = GetOption("Show Status Bar")
SetOption "Show Status Bar", True
afiles = GetFiles("C:\My Documents\", "*.mdb")
If IsEmpty(afiles(0)) Then
MsgBox "No Files Were Found", vbInformation
Else
For Each varfile In afiles
Debug.Print varfile
Next varfile
End If
SysCmd acSysCmdClearStatus
SetOption "Show Status Bar", booStatusBar
End Sub

Function GetFiles(ByVal vStrRootFolder As String, ByVal vStrSkelton As
String) As Variant
Dim aFolders As Variant
Dim varFolder As Variant
Dim afiles() As Variant
Dim strFile As String
aFolders = GetFolders(vStrRootFolder)
ReDim afiles(0)
For Each varFolder In aFolders
strFile = Dir(varFolder & vStrSkelton)
Do While strFile <""
SysCmd acSysCmdSetStatus, "Locating Files " & varFolder & strFile
afiles(UBound(afiles)) = varFolder & strFile
strFile = Dir()
ReDim Preserve afiles(UBound(afiles) + 1)
Loop
Next varFolder
If UBound(afiles) 0 Then ReDim Preserve afiles(0 To UBound(afiles) - 1)
GetFiles = afiles
Erase afiles
End Function

Function GetFolders(ByVal vStrRootFolder As String) As Variant
Dim aFolders() As Variant, strFolder As String, _
lngFolder As Long, lngStart As Long
If Right(vStrRootFolder, 1) <"\" Then vStrRootFolder = vStrRootFolder &
"\"
ReDim aFolders(lngStart)
aFolders(lngStart) = vStrRootFolder
Do
strFolder = Dir(aFolders(lngStart), vbDirectory)
Do While strFolder <""
If strFolder <"." And strFolder <".." Then
strFolder = aFolders(lngStart) & strFolder
If (GetAttr(strFolder) And vbDirectory) = vbDirectory Then
lngFolder = UBound(aFolders) + 1
If Right(strFolder, 1) <"\" Then strFolder = strFolder & "\"
ReDim Preserve aFolders(LBound(aFolders) To lngFolder)
SysCmd acSysCmdSetStatus, "Locating Folders " & strFolder
aFolders(lngFolder) = strFolder
End If
End If
strFolder = Dir()
Loop
lngStart = lngStart + 1
Loop Until lngStart UBound(aFolders)
GetFolders = aFolders
Erase aFolders
End Function

--
Lyle Fairfield

[Allen Browne]

Lyle identified some of the problems with FileSearch. There are others as
well (such as handling ZIP files inconsistently), so we gave up on it as
well.

Albert Kallal posted a good solution that uses collections recursively to
handle the subfolders. Here it is in an adapted form:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

You can use it as is, fill a list box with results, or adapt it to use the
collections any way you choose. The optional filespec works with ? and * as
wildcards.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alan Carpenter" <No*@iHome.nzwrote in message
news:Xn************************@yourdomain.com...

Access 8 on Win98 and WinXP

I'm having trouble with wildcards in the .Filename property of the
FileSearch Object giving different results on win98 and XP. I've been
successfully using FileSearch in win98 to fill an array with filenames
with
no problems since about 1998.

From the 97 Help:
(The Filename Property of the FileSearch object)
--------------------------
Returns or sets the name of the file to look for during a file search. The
name of the file may include the * (asterisk) or ? (question mark)
wildcards. Use the question mark wildcard to match any single character.
For example, type gr?y to match both "gray" and "grey." Use the asterisk
wildcard to match any number of characters. For example, type *.txt to
find
all files that have the .TXT extension. Read-write String.
----------------------------

Now that's fairly well spelled out, just what I want and just what I
expected.
It's what I get on my machine both from Access and Excel., *with or
without* a Reference to MSO97.DLL Without the Reference I can't use the
constants such as msoFileTypeAllFiles, but with the Reference I can find
that msoFileTypeAllFiles = 1 and use 1. That's the only constant I need,
and with "All Files" the next step in the selection path should be the
.Filename wildcard. The .Execute defaults for (SortBy, SortOrder,
AlwaysAccurate) are what I want, though I've noticed that although Help
says SortBy " ... can be either of the following MsoSortOrder constants:
msoSortOrderAscending or msoSortOrderDescending," when I omit SortBy I get
unsorted results. That's fine.

However, when my friend Kirk in another city runs it on his machine using
Office 8 under XP, he gets different results. A filespec of "*.doc;*.txt"
returns nothing, although there are files which match those specs.
Searching from File - Open Database - Advanced using those specs works
perfectly. (The Help indicates the FileSearch Object is used for that
search.)

I'm at a loss. Kirk has set the MSO97.DLL Reference and moved it as far up
the Reference list as it will stick.

Our "benchmark" is a search for a single asterisk. I return all files, he,
alas, gets nothing.

For what it's worth I'll paste my rather butchered test code.
ShowMe should work, but testgFindFiles() will need a valid path.

Can anyone tell my why a spec of "*.doc;*.txt" or a spec of "*" would
return nothing on an XP machine?

Cheers,
Alan
'################
Option Compare Database
Option Explicit
Public gFoundFiles As Variant

Function gFindFiles(findWhere, findWhat, _
Optional DoSubFolders As Boolean = True) As Variant
'Reference to Office C:\OFFICE97\OFFICE\MSO97.DLL

'findWhat = fixFindWhat(findWhat) ' For Kirk
Dim aFiles() As Variant
'Dim msg ' for Debug.
Dim I
'With Office.FileSearch
With Application.FileSearch
.NewSearch
'.LookIn = "n:\aa\" 'Force Backslash?Application.
If .LookIn = "C:\My Documents" Then Stop
'When findWhere doesn't exist, default is "My Documents"
'Maybe If Dir ... before "With ..
.LookIn = findWhere
.SearchSubFolders = DoSubFolders
.MatchAllWordForms = False
.MatchTextExactly = False
.FileType = 1 'msoFileTypeAllFiles
.Filename = findWhat '"*.doc;*.txt"
.Execute 'msoSortBy... 'RetVal could be useful.
ReDim aFiles(.FoundFiles.Count)
For I = 1 To .FoundFiles.Count
'msg = msg & .FoundFiles(I) & vbCrLf
aFiles(I) = .FoundFiles(I)
Next I
End With
' AlanPrint msg
gFindFiles = aFiles
'gFoundFiles = aFiles
Erase aFiles
End Function
Function fixFindWhat(findWhat)
Select Case findWhat
Case IsNull(findWhat), "*", "*.*"
fixFindWhat = ""
Case Else
fixFindWhat = findWhat
End Select
End Function

Sub testgFindFiles()
Dim Gottem
Dim thePath
'thePath = "I:\"
thePath = "H:\CDList\" 'Kirk, use whatever works.
Gottem = gFindFiles(thePath, "*.mp3")
Debug.Print UBound(Gottem), "*.mp3"
Gottem = gFindFiles(thePath, "*.wav")
Debug.Print UBound(Gottem), "*.wav"
Gottem = gFindFiles(thePath, "*.mp3;*.wav")
Debug.Print UBound(Gottem), "*.mp3;*.wav"
Gottem = gFindFiles(thePath, "*.*")
Debug.Print UBound(Gottem), "*.*"
Gottem = gFindFiles(thePath, "*")
Debug.Print UBound(Gottem), "*"
Gottem = gFindFiles(thePath, "")
Debug.Print UBound(Gottem), ""
Debug.Print "Done"
Erase Gottem
End Sub

Sub ShowMe()
Dim Gottem, msg
Dim thePath$, I
thePath = "c:\windows\"
Gottem = gFindFiles(thePath, "*.txt;*.ini", False)
For I = 1 To UBound(Gottem)
msg = msg & Gottem(I) & vbCrLf
Next
AlanPrint msg
End Sub
Sub AlanPrint(msg)
Dim PrintPlace$
PrintPlace$ = Environ("temp") & "\AlanTemp.txt"
Dim FileNum As Integer
FileNum = FreeFile
Open PrintPlace$ For Output As #FileNum
Print #FileNum, msg
Close FileNum
PrintPlace$ = "notepad.exe " & PrintPlace$
msg = Shell(PrintPlace$, 1) 'shrink msg variant
End Sub
'################

[Alan Carpenter]

Lyle Fairfield <ly***********@aim.comwrote in
news:Xn*********************************@216.221.8 1.119:

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

>Access 8 on Win98 and WinXP

I'm having trouble with wildcards in the .Filename property of the
FileSearch Object giving different results on win98 and XP. I've
been successfully using FileSearch in win98 to fill an array with
filenames with no problems since about 1998.

Many years ago (1999) we found that FileSearch did not succeed when it
came upon files that were not really files but shortcuts such a ".lnk"
files and ".pif" files. At that time FileSearch might error, but more
commonly it stopped searching, and "reported" that there were no more
files matching the pattern.

I've never experienced that with either LNK or PIF in Win98, but I have had
trouble with MAT (as you probably know, the shortcut created by dragging a
Table Icon from Access into an Explorer window.) From memory that would
lock Access to the point of a Reset to regain control. It wasn't the file
itself - I could get the same result by renaming Textfile.txt to
Textfile.mat.

At that time I wrote my own FindFiles UDF (below).
I expect that now there are other better, simpler solutions and that
someone will suggest one.

Thanks for that, Lyle.
I'd already written something very similar (last century!) which I replaced
with FileSearch because FileSearch had one big advantage - the Dir command
won't accept multiple specs ("*.txt;*.ini") the way FileSearch will.

On the other hand, with a spec of *.mdb, Dir will find DB1.MDB-BAD, but
FileSearch will ignore it - which again is what I want.

It's annoying to have somethng work perfectly (that is to say, the way I
want it to work) under Win98 but fail under XP

I may have to parse out the spec and make multiple passes. It would be nice
if someone knew a workaround to make FileSearch do what I want.

Cheers,
Alan

[Alan Carpenter]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:

Lyle identified some of the problems with FileSearch. There are others
as well (such as handling ZIP files inconsistently), so we gave up on
it as well.

Albert Kallal posted a good solution that uses collections recursively
to handle the subfolders. Here it is in an adapted form:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

You can use it as is, fill a list box with results, or adapt it to use
the collections any way you choose. The optional filespec works with ?
and * as wildcards.

Thanks for that, Allen

Unfortunately that routine also uses Dir as the workhorse, and Dir won't
accept multiple specs ("*.txt;*.ini") the way FileSearch will.

I'm not sure off the top of my head if the Access 98 ListBox has an
..AddItem Method, but I don't trust my memory. I've never been this old
before, so I may have got it wrong.

By the way, just to show that people *do* read your pages, a quote from
http://allenbrowne.com/ser-29.html

"Specifically test for and handle the complex data types if your code must
work with databases in Access 20007 or later"

Access 20007? I have to allow for possible changes for that long? Now I
*really* feel old!

Cheers,
Alan

[Alan Carpenter]

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:

Sorry, Allen. No .AddItem, so you added DirListBox()
I really am to old for this. :-)

Cheers,
Alan

[Allen Browne]

OOO dear! :-0

Thanks. Fixed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alan Carpenter" <No*@iHome.nzwrote in message
news:Xn************************@yourdomain.com...

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:

>Lyle identified some of the problems with FileSearch. There are others
as well (such as handling ZIP files inconsistently), so we gave up on
it as well.

Albert Kallal posted a good solution that uses collections recursively
to handle the subfolders. Here it is in an adapted form:
List files recursively - List files in a folder and subfolders
at:
http://allenbrowne.com/ser-59.html

You can use it as is, fill a list box with results, or adapt it to use
the collections any way you choose. The optional filespec works with ?
and * as wildcards.

Thanks for that, Allen

Unfortunately that routine also uses Dir as the workhorse, and Dir won't
accept multiple specs ("*.txt;*.ini") the way FileSearch will.

I'm not sure off the top of my head if the Access 98 ListBox has an
.AddItem Method, but I don't trust my memory. I've never been this old
before, so I may have got it wrong.

By the way, just to show that people *do* read your pages, a quote from
http://allenbrowne.com/ser-29.html

"Specifically test for and handle the complex data types if your code must
work with databases in Access 20007 or later"

Access 20007? I have to allow for possible changes for that long? Now I
*really* feel old!

Cheers,
Alan

[Lyle Fairfield]

Alan Carpenter wrote:

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:

Sorry, Allen. No .AddItem, so you added DirListBox()
I really am to old for this. :-)

Cheers,
Alan

In this message

http://groups.google.com/group/comp....1e22b83fef4ee8

Tom van Stiphout suggested shelling to a dos dir command piped to a
text file. By implication the text file could be opened and read and
the contents "split" into an array. I don't know if this might be
helpful for you or not, but it might be worth a look.

[Lyle Fairfield]

Lyle Fairfield wrote:

Alan Carpenter wrote:

"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:

Sorry, Allen. No .AddItem, so you added DirListBox()
I really am to old for this. :-)

Cheers,
Alan

In this message

http://groups.google.com/group/comp....1e22b83fef4ee8

Tom van Stiphout suggested shelling to a dos dir command piped to a
text file. By implication the text file could be opened and read and
the contents "split" into an array. I don't know if this might be
helpful for you or not, but it might be worth a look.

Well, I note that Tom did not say "Shell" and I think Shelling to Dir
may not be so easy.

[Alan Carpenter]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@h48g2000cwc.googlegr oups.com:

In this message

http://groups.google.com/group/comp....sg/dc1e22b83fe
f4ee8

Tom van Stiphout suggested shelling to a dos dir command piped to a
text file. By implication the text file could be opened and read and
the contents "split" into an array. I don't know if this might be
helpful for you or not, but it might be worth a look.

A good thought, but it raises another Win98/XP problem of waiting for DOS
to finish before continuing to read the new tet file. I've used (among
others) Terry Kreft's Shell2 method involving some Kernal32 calls and found
that DOS to Text read and parsed with VBA gave much faster performance for
huge drives than any of the pure VBA solutions.

The problem remains. From experience, the "Wait for DOS" methods work
differently in my Win98 and my friend's WinXP.

I feel now as I felt back using Access 2.0. It's hard to believe that
there's not a built in function which can do the simple Dir commands that
DOS did so well. Still, I forgive a lot for IntelliSense.

Cheers,
Alan

[Alan Carpenter]

"Lyle Fairfield" <ly***********@aim.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

>In this message

http://groups.google.com/group/comp....msg/dc1e22b83f
ef4ee8

Tom van Stiphout suggested shelling to a dos dir command piped to a
text file. By implication the text file could be opened and read and
the contents "split" into an array. I don't know if this might be
helpful for you or not, but it might be worth a look.

Well, I note that Tom did not say "Shell" and I think Shelling to Dir
may not be so easy.

Also,he was after a list of Folders only. Still, the principle's the same
and I still have my Access 2.0 code for parsing results.

I have assorted Classes provided to CDMA through the years but nothing was
quite as FileSearch. If only it worked on XP as it does in Win98 I'd have
everything I need.

Cheers,
Alan

[David W. Fenton]

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

I'm not sure off the top of my head if the Access 98 ListBox has
an .AddItem Method, but I don't trust my memory.

Good thing you're not trusting it, as there is no such thing as
Access 98.

The .AddItem method was added to listboxes after A97, but I don't
know which version. I don't use anything other than A2K format, so
would never get to use it. I think it was introduced in A2K3. I
can't say I've ever needed it, myself.

Actually, the A2K3 online help says it is available in A2K2 and
A2K3, but the A2K2 help doesn't mention it, so far as I can tell (a
search for "additem" returned nothing!).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Alan Carpenter]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

>I'm not sure off the top of my head if the Access 98 ListBox has
an .AddItem Method, but I don't trust my memory.

Good thing you're not trusting it, as there is no such thing as
Access 98.

Thanks, David. I'm resigned to the fact that I will continue to make the
same slip, no matter how often I decide to stick with either Access 97 or
Access 8.0 to identify ... um the one we're talking about.

I've used AddItem only in VB6. For Access - hang the expense, I like
Comboboxes.

Cheers,
Alan

The .AddItem method was added to listboxes after A97, but I don't
know which version. I don't use anything other than A2K format, so
would never get to use it. I think it was introduced in A2K3. I
can't say I've ever needed it, myself.

Actually, the A2K3 online help says it is available in A2K2 and
A2K3, but the A2K2 help doesn't mention it, so far as I can tell (a
search for "additem" returned nothing!).

[Alan Carpenter]

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

Access 8 on Win98 and WinXP

I'm having trouble with wildcards in the .Filename property of the
FileSearch Object giving different results on win98 and XP. I've been
successfully using FileSearch in win98 to fill an array with filenames
with no problems since about 1998.

Thanks to all for the help.
I hadn't realized there were so many problems with FileSearch, and the fact
that the Office team has kicked FileSearch back to the Windows team doesn't
inspire confidence.

Doug Steele has showed some time trials using Dir, FileSystemObject and API
calls, along with an example of using PathMatchSpecW from the Shell Light-
weight Utility Library "shlwapi" (sounds like another Marx Brother) on the
Microsoft Access Advisor site at http://advisor.com/doc/16279

I intend to play with the code for a while to see what I can break.

Cheers,
Alan

[David W. Fenton]

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

I hadn't realized there were so many problems with FileSearch, and
the fact that the Office team has kicked FileSearch back to the
Windows team doesn't inspire confidence.

I don't use anything that requires a reference beyond the three
default Access references. I do use the File System Object for
network detection, but using late binding. I presume one can use the
FileSearch object with late binding, but given its problems, it
seems it's hardly worth the effort.

BTW, is there some easy way to figure out what the syntax is for
initiating a COM object using its name, i.e., the argument supplied
to CreateObject()? I search the Registry for this, but there's
surely a better way?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

I notice that nobody has brought up using API calls. API calls allow you
to be more flexible in how the code runs (ie not a black box like
FileSearch ) and they are fast.

I found some code a long time ago that I modified for my own purposes. I
can't remember the original source as the comments in the code never
listed an author. I just checked the VBnet site (NOT VB.Net, classic VB)
and they have a whole section on File API functions at:
http://vbnet.mvps.org/index.html?cod...api/index.html

The function that looks closest to what you are looking for is called
'FindFirstFile: Recursive File Search (minimal code)' and it is at:
http://vbnet.mvps.org/index.html?cod...es_minimal.htm

These are written with VB in mind, but are very easily modified to work
with Access. Most of the changes will involve how you load the results
into a control since listboxes are different in AC than VB, but getting
the results requires no changes.

The code I have takes the files and loads them into a Recordset vs into
a Listbox. If you want to see that I can post it.

Hope that helps.

--
Bri

[DriverDB]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

BTW, is there some easy way to figure out what the syntax is for
initiating a COM object using its name, i.e., the argument supplied
to CreateObject()? I search the Registry for this, but there's
surely a better way?

Sorry, David, but I don't know enough about that to be of any practical
use.
Any time I've needed the Name I just read it of the documentation and type
it in. :-)

It does seem that using the Registry can't be bad, though, because there
may be more informtion there.

At "cat sat on mat" level, If you open

http://www.15seconds.com/issue/971214.htm

... search for ProgIds and read those three or four paragraphs, Wayne
Berry has given and example of why the name might not be easily available.

Cheers,
Alan

[DriverDB]

Bri <no*@here.comwrote in news:L4u_g.172508$R63.65808@pd7urf1no:

I notice that nobody has brought up using API calls. API calls allow you
to be more flexible in how the code runs (ie not a black box like
FileSearch ) and they are fast.

I found some code a long time ago that I modified for my own purposes. I
can't remember the original source as the comments in the code never
listed an author. I just checked the VBnet site (NOT VB.Net, classic VB)
and they have a whole section on File API functions at:
http://vbnet.mvps.org/index.html?cod...api/index.html

The function that looks closest to what you are looking for is called
'FindFirstFile: Recursive File Search (minimal code)' and it is at:
http://vbnet.mvps.org/index.html?cod...es_minimal.htm

These are written with VB in mind, but are very easily modified to work
with Access. Most of the changes will involve how you load the results
into a control since listboxes are different in AC than VB, but getting
the results requires no changes.

The code I have takes the files and loads them into a Recordset vs into
a Listbox. If you want to see that I can post it.

Thanks, Bri. Iended up with something almost identical to the
Private Sub SearchForFiles(FP As FILE_PARAMS)

I've done two things differently.

One, I'm using

Private Declare Function PathMatchSpec _
Lib "shlwapi" Alias "PathMatchSpecW" ( _
ByVal pszFileParam As Long, _
ByVal pszSpec As Long) As Long

to handle the multiple Spec for the type of file I'm after.

The other main difference is that I need this to work both in Access and
Excel, so to make life easy I stuff each Pathname returned into a
Collection. I've wrapped the whole thing up to match my original routine,
which returned an Array of 1 to UBound with zero for me to use. A bit
wasteful, but it means I can just plug it in wherever I was using
FileSearch.

I owe you big thanks for the link, though. If I hadn't read that page I
wouldn't have noticed that even after setting up FindClose() I'd forgotten
to actually use it.:-)

Cheers,
Alan

[David W. Fenton]

DriverDB <DriverDB@homewrote in
news:Xn**************************@yourdomain.com:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

>Alan Carpenter <No*@iHome.nzwrote in
news:Xn************************@yourdomain.com:

BTW, is there some easy way to figure out what the syntax is for
initiating a COM object using its name, i.e., the argument
supplied to CreateObject()? I search the Registry for this, but
there's surely a better way?

Sorry, David, but I don't know enough about that to be of any
practical use.
Any time I've needed the Name I just read it of the documentation
and type it in. :-)

It does seem that using the Registry can't be bad, though, because
there may be more informtion there.

At "cat sat on mat" level, If you open

http://www.15seconds.com/issue/971214.htm

... search for ProgIds and read those three or four paragraphs,
Wayne
Berry has given and example of why the name might not be easily
available.

That caveat seems to me not to apply in Access, as any component
that you could set a reference for would be one that you're allowed
to use directly. I try to set the fewest number of references using
late binding, but I only utilize components for which it's possible
to set a reference.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Alan Carpenter]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

>>
http://www.15seconds.com/issue/971214.htm

... search for ProgIds and read those three or four paragraphs,
Wayne
Berry has given and example of why the name might not be easily
available.

That caveat seems to me not to apply in Access, as any component
that you could set a reference for would be one that you're allowed
to use directly. I try to set the fewest number of references using
late binding, but I only utilize components for which it's possible
to set a reference.

Perhaps if you asked the same question in a new thread you'd get more
useful replies from those with more experience at the task than I have.
Most of my thoughts about it end up in a "chicken and egg" loop :-)

Cheers,
Alan