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.
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