Using VBA to find file path

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
Hi

here is the code:

Code:
Sub findfile()
    Dim FoundIt As Boolean
    Dim FileToGet As String
    FoundIt = False
    FileToGet = "hello.xls" 'can be part or all of a file
    If Len(FileToGet) = 0 Then Exit Sub
    Set fs = CreateObject("scripting.filesystemobject")
    For Each dr In fs.Drives
        Application.StatusBar = "Searching " & dr
        If Not FoundIt Then
            With Application.FileSearch
                .LookIn = dr
                .SearchSubFolders = True
                .Filename = FileToGet
                .Execute
                If .FoundFiles.Count > 0 Then
                    For i = 0 To .FoundFiles.Count - 1
                        Range("order!ea1").Formula = WorksheetFunction.Substitute(UCase(.FoundFiles.Item(i + 1)), UCase(.Filename), "")
                        FoundIt = True
                    Next i
                End If
            End With
        End If
    Next dr
    If Not FoundIt Then Range("order!eA1").Formula = "Not Found"
    Application.StatusBar = False
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
no question. just contributing an answer i was able to find/figure out. thought it might be useful for a searcher in the future.


FINE :)
and where is the question :confused:
(yes, I've seen the topic title)
 
Upvote 0
And this is the answer to what?:)
 
Upvote 0
to a question i asked that received no reply, which should be reasonably clear from the title.

the code, if your're not sure what it does, will find the exact location of a file on your computer and print the complete filepath in a cell. you can even use just a part of the file name. handy piece of code.

as to what it is useful for is related to other unanswered questions i researched and figured out on my own, and also posted the solutions.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top