How do I use Excel to search for files on my computer?

Great Question!

This one is a tough one and it took me quite bit of time to get right.

I was having a problem with a program I created for some users, the program was referencing files as variables on their machine. But the problem was, the files would be in different locations based on the user’s habits.

Some people kept the files in folders in the C:\ drive, some people had a folder on their desktop, other’s had shared drives synced to their machines where they kept their files. There was no rhyme or reason where the file would be.

The answer? CREATE A FUNCTION!

The below function allows you to use a formula in any sheet that will return the file path, much like any formula you use everyday.
A black box may show up while this runs, but don’t worry, that’s normal.
As always, between the ( and ) put the cell with the filename you are trying to find.

=FindFilePath()

In a new module in your VBE, add the below code:

Function FindFilePath(Thing1 As String) As String

Dim FindMe As String

FindMe = Split(CreateObject("WScript.Shell").Exec("CMD /C FOR /r ""C:\"" %i IN (*" & Thing1 & ") DO (ECHO %i)").StdOut.ReadAll, vbCrLf)(2)
FindFilePath = Left$(FindMe, InStrRev(FindMe, "\"))

End Function
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s