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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
And this is the answer to what?:)
 

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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.
 

Forum statistics

Threads
1,141,596
Messages
5,707,303
Members
421,502
Latest member
PULBAG

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
Top