Patternmatch

[Chip]

I am trying to create a list of all the files in a particular directory

(including subdirectories).

The Patternmatch property of the FileFind object looks like it should
do the trick.

What's wrong with this code? As it stands, it just returns two files
that happen to have asterisks in their filenames...

Public Sub grabfiles()
With Application.FileFind
.Options = msoOptionsNew
.PatternMatch = True
.FileName = "*"
.SearchSubFolders = True
.SearchPath = "Your disk:Your path"
.Execute
With .FoundFiles
If .Count > 0 Then
MsgBox "There were " & .Count & _
" file(s) found."
For i = 1 To .Count
mysize = FileLen(.Item(i))
MsgBox .Item(i) & " " & mysize
Next i
Else
MsgBox "There were no files found."
End If
End With
End With
End Sub

 

[Jim Gordon MVP]

Hi Chip,

What's wrong is that FileFind has been turned off by Microsoft. It no
longer works.

A couple years ago I posted a message that detailed my experience trying
to convince Microsoft to restore FileFind. I compared FileFind to an
errant relative who had gone to prison and that no one wants to talk
about it.

There's something about FileFind that is a real sore subject at
Microsoft. Nonetheless, it certainly wouldn't hurt to explain your
business case for restoring that functionality. Do it in terms of the
number of additional units of MacOffice that might be sold as a result
of restoring the feature.

It appears that Microsoft has just changed all of the ways for the
public to report problems to them.

The old web page for reporting bugs is here:
http://register.microsoft.com/mswish/suggestion.asp
Comments sent to that page were (maybe still are?) sent to the product
managers.

However, I just tried the feedback feature on the Help menu and wound up
here http://support.microsoft.com/gp/contactbug
That's a toll-free telephone number to report bugs.

I consider FileFind not working to be a bug. They tried to turn the help
topics off for FileFind, but some of those still work.

You can be sure that the MVPs are trying to get FileFind re-instated.
However, please be sure to make your feelings known, otherwise Microsoft
won't know that this is important to customers.

Meanwhile, maybe Paul will jump in with an AppleScript solution.

-Jim

 

[Bernard Rey]

Right Jim. I fully agree and miss that function too!! But FileFind did run
in Excel X and, from his description, I suppose Chip is running his VB Macro
in Excel X.

In my opinion, the problem Chip describes is rather that he's trying to use
the "*" character as a wildcard. And you can't have that on the Mac side.
It's just considered as an asterisk therefore giving the described result.

Chip, what happens if you simple delete that Line (.Filename = "*")?

 

[Bernard Rey]

I guess I was too fast : FileFind seems to be very buggy in Excel X. It's in
Excel:2001 that it can run.

And in Excel:2001, The PatternMatch Property will allow the "?" and "*"
wildcards, but only in the "Text" Property. So you can't use it with the
"Filename" Property. Try the "Text" Property instead (if you're using
Excel:2001, of course).

 

[Paul Berkowitz]

You can be sure that the MVPs are trying to get FileFind re-instated.
However, please be sure to make your feelings known, otherwise Microsoft
won't know that this is important to customers.

Meanwhile, maybe Paul will jump in with an AppleScript solution.

This is pretty simple in AppleScript. There's no particular reason why you
would want to depend on Excel, of all things, to look for files on your hard
disk.

This is the simplest way:

set theFolder to (choose folder)
tell application "Finder"
try
set theFiles to entire contents of theFolder as alias list
on error -- if there's only one file, a Finder bug
set theFiles to entire contents of theFolder as alias as list
end try
end tell


set theCount to (count theFiles)
if theCount > 0 then
display dialog "There were " & theCount & " file(s) and folder(s)
found."
repeat with i from 1 to theCount
set theFile to item i of theFiles
set fileInfo to info for theFile
if not (folder of fileInfo) then -- files only
set mySize to size of fileInfo
display dialog (theFile as string) & ": " & mySize & " bytes"
else
display dialog (theFile as string) & " (folder)"
end if
end repeat
else
display dialog "There were no files found."
end if



Now if you're gong to need to do something in VBA which often requires just
the string path, not the AppleScript 'alias' class (a file, basically) then
follow the Finder tell block with:

repeat with i from 1 to (count theFiles)
set item i of my theFiles to (item i of my theFiles as string)
end repeat


That will give you a list of all the file paths.

You can put the whole thing (or as much as you need) into a VBA macro using
the MacScript function, where you need to escape every AppleScript " quote
sign with "" and need to put each line of the original AppleScript into its
own " quotes, followed by & _ . You can use your own MsgBox code instead
of the second section.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Paul Berkowitz]

This is the simplest way:

set theFolder to (choose folder)

or

set theFolder to alias "Your disk:Your path:" -- note colon at end

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.