Findfile fails

[Eric Koskoff]

I'm running OS X 10.3.3. I've tried the posted solutions as well as
the example code from the Microsoft VBA help installed with Office X
10.1.5. Nothing works and I've tried every combination I can think of.
The results are one of 3:
1. 0 files found
2. Too many files found (both searchpath and filename are set to valid
names)
3. Excel crashes and exits with no error message (I can't even trap
the error with an On Error statement.) I do get a prompt to send a
problem report to Apple after Excel quits.

Does anyone have a suggestion?

Thanks.

 

[Jim Gordon MVP]

Hi Eric,

Unfortunately FindFile does not work beyond Excel 2001 on the Mac and at
some point it was dropped from Windows Excel, too. Unfortunately Microsoft
did not update the help files to reflect that information.

You can send feedback to Microsoft requesting that FindFile be restored, by
using the Help menu feedback feature. Unfortunately I get the feeling
there's nothing that can be done to restore it, though.

You should be able to accomplish the task using directory commands. If you
get stuck, please post back.

-Jim

 

[Eric Koskoff]

Hi Jim,

Thanks for the quick response! I'll give the directory commands a try
and get back to you if I can't figure that out.

I have found many disconnects between VBA on the PC and Mac and this
is just one more. Microsoft's failure to update the documentation is
unforgivable. It could have saved me hours of work and frustration.
I'll submit a feedback request but I've found in the past they don't
care. I've even offered to be a beta site for new releases but have
never had the courtesy of a response.

On the PC FileSearch works flawlessly in the current version of Excel
(under XP Pro.)

Again my thanks.
Eric

 

[Jim Gordon MVP]

FileSearch is a hidden method in the object browser but I have not tried it
so I can't verify whether or not it still works. If it is part of FindFile
it won't work.

-Jim

 

[Eric Koskoff]

Hi Jim,

Do you have any sample code for searching through directories to find
a specific named file? I've tried using the Dir command but haven't
figured out how to step through directories and subdirectories and
look for files. Also I can't figure out how to check other disks
mounted on the desktop to search those.

Thanks,
Eric

 

[Jim Gordon MVP]

Hi Eric,

You're not gonna like this, but yes it can be done. No I won't tell you how.

Once I had to write a routine that cycled through directories to fix a
mistake on our staff computers. I almost lost my job because I would not
tell my boss the code. You'll know why I wouldn't reveal the code if you
figure out how to do it, and you'll understand why I won't post it anywhere.

I was able to figure out how by using Excel's help files.

-Jim

 

[Eric Koskoff]

Hi again,

Well, I guess I can understand your concern. Unless you're planning to
sell the code I''m not sure why you're keeping it secret but that's
your choice.

I've spent hours reading and rereading the help files and trying
hundreds of different approaches. Some things work partially but not
completely or reliably. Before I continue to try, could you at least
tell me whether you've tried the code you wrote under 10.3.3 with
Office v.X 10.1.5? Things that worked before don't now.

Regards,
Eric

 

[Eric Koskoff]

Hi again,

Well, I guess I can understand your concern. Unless you're planning to
sell the code I''m not sure why you're keeping it secret but that's
your choice.

I've spent hours reading and rereading the help files and trying
hundreds of different approaches. Some things work partially but not
completely or reliably. Before I continue to try, could you at least
tell me whether you've tried the code you wrote under 10.3.3 with
Office v.X 10.1.5? Things that worked before don't now.

Regards,
Eric

 

[Eric Koskoff]

Hi again,

Well, I guess I can understand your concern. Unless you're planning to
sell the code I''m not sure why you're keeping it secret but that's
your choice.

I've spent hours reading and rereading the help files and trying
hundreds of different approaches. Some things work partially but not
completely or reliably. Before I continue to try, could you at least
tell me whether you've tried the code you wrote under 10.3.3 with
Office v.X 10.1.5? Things that worked before don't now.

Regards,
Eric

 

[Eric Koskoff]

Hi again,

Well, I guess I can understand your concern. Unless you're planning to
sell the code I''m not sure why you're keeping it secret but that's
your choice.

I've spent hours reading and rereading the help files and trying
hundreds of different approaches. Some things work partially but not
completely or reliably. Before I continue to try, could you at least
tell me whether you've tried the code you wrote under 10.3.3 with
Office v.X 10.1.5? Things that worked before don't now.

Regards,
Eric

 

[Jim Gordon MVP]

Hi Eric,

I bet you are surprised to see that your message is repeated in the
newsgroup several times. That happens once in a while. A mystery.

The code to accomplish what you are asking is just a millimeter away from
being a computer virus, so I don't talk to anyone about how to go about
doing it. Sorry.

If you could elaborate a bit about the things that aren't working for you
perhaps we can offer some suggestions. Do you have a line of code that's not
executing the way you expected? If so, please post a message with the
details.

-Jim
--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Eric Koskoff]

Hi Jim,

Yes, I saw what happened and was very puzzled. No way to delete the
extras that I can see.

In the meantime I have contacted Microsoft and they've sent me some
code that will search through a specified directory. Since that's only
part of what I want I've requested they continue to search for a
solution that allows a search of all attached drives and directories.
Since I develop code for clients and can not guarantee where they
choose to install the code I need that ability.

BTW Microsoft confirmed that FindFile will not be supported in Office
2004. They claim it's too "unstable." Strange. They are suggesting
that AppleScript or eScript be used instead.

As for specific code, I really can't provide any yet. The major
problems I've run into are:

1. Using the "vbDirectory" parameter does not return only directories.
(The documentation acknowledges this.)
2. I can't access anything but a NAMED drive (e.g. "Macintosh HD") and
have found no way to determine the name of the primary drive or to
access other drives.
3. Once a null return is reached after a directory search I can't
figure out how to move to the next directory without an error.

I'm reluctant to put code out on the web and would be happy to send it
to you privately if you want to send me your email address. Mine is
correctly listed with my posts. I tried sending you a message but it
was returned.

Thanks,
Eric

 

[Jim Gordon MVP]

Hi Eric,

Strange is a good word to describe the situation. A Google search of
"Macintosh FindFile unstable" turns up just 2 hits - this thread and another
one that's not related to FindFile being unstable. So FindFile being
unstable is not a very likely reason for it being ditched in Office 2004. I
have no clue as to why FindFile has been banished.

At any rate, I found a code sample in a Google thread that may be of service
to you:
<http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=OouUrvJlDHA.2512
%40TK2MSFTNGP09.phx.gbl&rnum=11&prev=/groups%3Fq%3Dvba%2Bloop%2Bthrough%2Bdi
rectories%26start%3D10%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DOouUrvJlDHA.2
512%2540TK2MSFTNGP09.phx.gbl%26rnum%3D11>

I sell a PowerPoint add-in that used FindFile, and I've not found a really
good substitute for FindFile. I dread having to write my own "Browse"
button, but some day when I have time I might just do it.

You should be able to figure out my real email address. Just think in terms
of "hot" being "warmer than warm."

-Jim

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info