Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

How get the path of the FoundFile after a search?

Latest post Tue, Jun 22 2010 1:44 PM by Bob Phillips. 1 replies.
  • Tue, Jun 22 2010 9:49 AM

    • Matt
    • Top 150 Contributor
    • Joined on Wed, Jun 16 2010
    • South Carolina
    • Posts 6
    • Points 142

    How get the path of the FoundFile after a search?

    I am trying to search for a file and after the file is found I need the path put in a string to be used later in the macro.  This is the block of code I have so far:

    Dim avg As String

    With Application.FileSearch
            .NewSearch
            .LookIn = "C:\My Documents"
            .TextOrProperty = "*Average*.xls"
            .MatchTextExactly = True
            .FileType = msoFileTypeExcelFiles
            avg = .FoundFiles     ' This is the part that I am having a problem with.  I want to put the path in a string.
        End With

    I want the FileSearch to look for a file that includes the word Average in the name, then I want to get the path for that file and use it later in the macro.

    There will be only one file with Average in the name, but it may be in different places in C:\My Documents, depending on which computer the macro is run on.

    I don't know if I am on the right track with this or if there is an easier way.

    Thanks for helping.

     

    Filed under: ,
    • Post Points: 21
  • Tue, Jun 22 2010 1:44 PM In reply to

    Re: How get the path of the FoundFile after a search?

    Try

    Dim avg As String

        With Application.FileSearch
       
            .NewSearch
            .LookIn = "C:\My Documents"
            .Filename = "*Average*.xls"
            .FileType = msoFileTypeExcelWorkbooks
            If .Execute() > 0 Then
           
                avg = .FoundFiles(1)
            End If
        End With

    Regards

    Bob

    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.