Find A File Without Knowing Which Drive It's On 1

[ExcelWorker]

Someone out there might know a faster way of doing this but this subroutine works. It will let you find a file by giving it only the filename.
It will return the FULL path name.
-----------------------------------------------------
Option Explicit
Dim myFS As Object

Sub test()
Dim myDirPath As String

MY_FindFile "FY09 Workload Program.xls", myDirPath
Debug.Assert False

End Sub

Sub MY_FindFile(myFilename As String, myReturnedPath As String)
Dim myPath As String
Dim myTemp As String
Dim myFolder As Object
Dim mySub As Object
Dim myItem As Object
Dim myDriveCount As Integer

Set myFS = CreateObject("Scripting.FileSystemObject")
myDriveCount = 66
myPath = "A:\"
ReTestPath:
If myFS.driveexists(myPath) = False Then GoTo NextDrive
Set myFolder = myFS.GetFolder(myPath)
Set mySub = myFolder.subfolders
For Each myItem In mySub
myTemp = TestSub(myFilename, myItem.Path & "\")
If myTemp <> "" Then
myReturnedPath = VBA.Left(myTemp, VBA.InStrRev(myTemp, "\"))
Exit Sub
End If
NextItem:
Next

NextDrive:
myPath = VBA.Chr(myDriveCount) & ":\"
myDriveCount = myDriveCount + 1
GoTo ReTestPath

End Sub


Function TestSub(myFilename As String, myNewPath As String) As String
Dim myFolder As Object, mySub As Object, myItem As Object
Dim myTemp As String

If myFS.fileexists(myNewPath & myFilename) = True Then
TestSub = myNewPath & myFilename
Exit Function
End If
Set myFolder = myFS.GetFolder(myNewPath)
Set mySub = myFolder.subfolders
On Error GoTo TestError
For Each myItem In mySub
myTemp = TestSub(myFilename, myItem.Path & "\")
If myTemp <> "" Then
TestSub = myTemp
Exit Function
End If
Next
Exit Function

TestError:
If Err.Description = "Permission denied" Then Exit Function
Debug.Assert False

End Function

 

[HarleyQuinn]

What happens if there are multiple files that match the filename parameter and the first one it finds isn't the right one?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[fumei]

1. Please use the TGML code tags when posting code. Thanks.

2. The code fails for me. It explicitly sets myPath:

myPath = "A:\"

so that if the machine HAS a floppy drive (usually A, then the following will ALWAYS return True.

If myFS.driveexists(myPath) = False Then GoTo NextDrive

It will never return False (because the Drive exists), thus the next lines will execute:

Set myFolder = myFS.GetFolder(myPath)

which for me fails as a run-time error 76 - "Path not found" - because there IS no folder to get...as there is no disk in A:

Of course if there is a disk in A: then it would not fail, but if there is no disk in A:, then it does. Methinks this should be part of an Else statement.

3. Are you using late-binding? If not, it would be probably be better to use:

 Dim myFS As Scripting.FileSystemObject

rather than:

 Dim myFS As Object

Gerry

 

[ExcelWorker]

Did not think of that, my computer does not have a floppy drive.

 

[fumei]

And there you go....

The code (as it is) will always fail if there is a floppy in the machine (and it is A.

If you do not have a floppy, is your primary hard drive A:? If it is, then I still do not understand the use of the instruction:

If myFS.driveexists(myPath) = False Then GoTo NextDrive

if you are explicitly set myPath to "A:\".

BTW: VBA no longer actually uses Integers. It automatically converts all declared Integers like:

Dim myDriveCount As Integer

to Long.

Gerry

 

[fumei]

Here is a slight alternative, using one function. FSO is early-binding, so objects are explicitly declared as FSO objects. The test file is "Basic Concepts.doc", and the full path is:

c:\zzz\Yadda_B\Basic Concepts.doc

Option Explicit

Sub testFind()
MsgBox MY_FindFile("Basic Concepts.doc")
End Sub

Function MY_FindFile(strIn As String) As String

Dim myFS As Scripting.FileSystemObject
Dim myDrive As Scripting.Drive
Dim myFolder As Scripting.Folder
Dim mySub As Scripting.Folder
Dim mySubSub As Scripting.Folder
Dim myFile As Scripting.File
Dim DriveName As String
Dim j As Long
Dim p As Long

j = Len(strIn)

Set myFS = CreateObject("Scripting.FileSystemObject")

For Each myDrive In myFS.Drives
   If myDrive.DriveLetter <> "A" Then
   DriveName = myDrive.DriveLetter & ":\"
   ' check the root folder
   ' who knows, it may be there
   For Each myFile In myDrive.RootFolder.Files
     If myFile.Name = strIn Then
         p = Len(myFile.Path)
        MY_FindFile = Left(myFile.Path, p - j)
        GoTo myExit
     End If
   Next
   ' set folder object for root
   Set myFolder = myFS.GetFolder(DriveName)
   '  files already checked for root
   '  so start iterating subfolders
   For Each mySub In myFolder.SubFolders
      For Each myFile In mySub.Files
         If myFile.Name = strIn Then
            p = Len(myFile.Path)
            MY_FindFile = Left(myFile.Path, p - j)
            GoTo myExit
         End If
      Next
      For Each mySubSub In mySub.SubFolders
         For Each myFile In mySubSub.Files
            If myFile.Name = strIn Then
               p = Len(myFile.Path)
               MY_FindFile = Left(myFile.Path, p - j)
               GoTo myExit
            End If
         Next
      Next
   Next
   End If
Next
myExit:
If MY_FindFile = "" Then
   MsgBox "File not found."
End If
End Function

Result? c:\zzz\Yadda_B\

No doubt it could be made cleaner, with better iterations of subfolders (a check should be made to see if each subfolder has itself a subfolder count, etc. etc. etc.), but what the heck.

Gerry

 

[macropod]

Hi Gerry,

What if you've also got a Drive B (one of my PCs has) - and the file might be on a diskette that might be in either A or B?

(Just being perverse) ...


Cheers
[MS MVP - Word]

 

[JTBorton]

Yah, you guys are miles above me, but can you just let the user find the file for you? Why not use the common dialog control?

dlgCommon.Filter = "All Files *.*|*.*"
dlgCommon.ShowOpen
FilePath = dlgCommon.FileName

Just throwing it out there...

-JTBorton
Another Day, Another Disaster

 

[HarleyQuinn]

Gerry

VBA no longer actually uses Integers. It automatically converts all declared Integers like:

Really, didn't know that. On that line of thought, if it's converted to a Long, how come you can't assign a value larger than an Integer to integers declared like that?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[fumei]

macropod: of course. Why not? Absolutely. I hoped I had, in fact, pointed out that any truly useful function should have full and proper error trapping...and this one (the one I posted) does not.

HQ: "On that line of thought, if it's converted to a Long, how come you can't assign a value larger than an Integer to integers declared like that? "

Well, i am guessing here, but...because of the weirdness of VBA. When VBA internally processes/actions:

Dim intYadda As Integer

it does so after it has run the instructions through the parser and converted the integer to a long.

However, an explicit instruction:

intYadda = BIG_NUMBER_BIGGER_THAN_INTEGER

may fail as VBA still understand what an integer is (you can still declare one)...it just does not use them any more.

To tell the truth, I have never actually tried to assign a value too big to a declared integer. What actually happens???? Does it fails as a run-time, or a compiling error?

Gerry

 

[fumei]

Just as a point, I do not know of any numeric value of anything in Word that is NOT a Long.

Even something like EndOf (used to extend a selection or range to the End Of a sentence, paragraph, but alos Section and Story) is a Long. Now, true, moving an EndOf for a Story could require a value greater than 32K, but a sentence? Not likely.

And other weirdness. For example the Function FileAttr is formally declared as:

Function FileAttr (FileNumber As [i]Integer[/i], _
                   [ReturnType As [i]Integer[/i]]) _
                   [b]As Long[/b]

So you have to feed the function Integers (as the parameter(s) are declared as such)...which are converted to Long, processed, with the result a Long.

We are going off into the weeds here. Perhaps another thread?

Gerry

 

[HarleyQuinn]

If you try and assign a long value to an Integer type it will fail at run time with an 'Overflow' error.

I've not got a lot of time at the moment but I would imagine that the reason that FileAttr returns a long is that it's a wrapper for one of the File API's (I'm not sure which one off the top of my head) which will return a long. Similar to a discussion strongm and I had in a thread in the VB6 forum regarding adding items to listboxes and the limits enforced when using the listboxes methods (and how the methods are wrappers and if you ignore them and call the API's directly the limitations are bypassed).

You are right though, we're way off topic. I'd agree that if we want to continue this we should start another thread.

Cheers

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[fumei]

Perhaps. I was not commenting on the fact that FileAttr returns a Long, but the fact its parameters are declared as Integers. Which means...VBA converts those to Long (during parsing), then - I guess - BACK to integers for the Function?

Weeds.



So.....how's the FileSearch coming?



Gerry

 

[HarleyQuinn]

Yes, we laugh but it's coming along nicely

You might even have some code to test in the not too distant future

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com