Hi all,

I have code which searches for a file on the computer, and returns its path (the File Search property, and a API function)

However, this takes some time and I was wondering of there is another function which returns a file's path quickly.

I noticed that you can get the path of a workbook very fast, so I presume there is a funtion which can do the same to any kind of file.

thanks
-Max

Max -

Have you tried Application.GetOpenFilename and Application.Pathfinder?

Application.GetOpenFilename allows the user to click on the file he wants to open, similiarly to Excel->Open. It returns with full file name, including path. You can then use Application.Pathfinder to extract the file name only.

Kathy

I know those options, except for application.pathfinder (this option is not available in VBA somehow)

I would like to quickly search for a file on a drive and get its path, without asking the user for input. It must be an automated search. I know a few ways, but Id like to hear other people use to search for files quickly

-max

Hi Max,

I don't know if this is any help but it seems reasonably fast to me.

Sub SearchForFiles()

  Dim i As Long
  Dim fs As FileSearch

  Set fs = Application.FileSearch
  With fs
    .NewSearch
    .LookIn = "C:\"
    .SearchSubFolders = True
    .Filename = "*.xla"
    .MatchTextExactly = False
    If .Execute() > 0 Then
      MsgBox "There were " & .FoundFiles.Count & " file(s) found."
      For i = 1 To .FoundFiles.Count
        ActiveSheet.Cells(i, 1).Value = .FoundFiles(i)
      Next i
    Else
      MsgBox "There were no files found."
    End If
  End With
  Set fs = Nothing

End Sub

Cheers
nic

I just came across some of your other stuff relating to Application.FileSearch and its propensity to fail so I tried some quick experiments. This may be a red herring and you might of tried it but I found that I could get FileSearch to fail on a machine that it worked on previously if I enabled Indexing Service. If I then stop Indexing Service FileSearch works again. I don't know if this is repeatable on other machines but it seems reasonably consistent on my machine.

Cheers
nic