Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Levin, Manawatu-Wanganui, New Zealand
    Thanked 28 Times in 26 Posts

    vba 2007 .filesearch (Office 2007)

    Greetings to the lounge

    Not all in vba for 2007 is backward compatible.

    The application.filesearch has been hidden.

    I had a routine that populated a listbox with filename returned with the application.filesearch & foundfiles

    'With Application.FileSearch
    ' .LookIn = strFileLocation
    ' .Execute (msoSortByFileName)
    ' For i = 1 To .FoundFiles.Count
    ' If Right(.FoundFiles(i), 4) = ".doc" Then
    ' ListBox1.AddItem Mid(.FoundFiles(i), Len(strFileLocation) + 1)
    ' End If
    ' Next i
    'End With

    This fails with Office 2007
    My work around follows.
    A reference to the microsoft scripting runtime is required

    Dim fso As New FileSystemObject
    Dim oFldr As Folder
    Dim oFl As File
    dim strFileLocation as string

    strFileLocation = "somepath"

    ' Use this snippet for office 2007
    Set oFldr = fso.GetFolder(strFileLocation)
    For Each oFl In oFldr.Files
    If Right(oFl.Name, 4) = ".doc" Then
    ListBox1.AddItem oFl.Name
    End If


    Hope someone finds this useful.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: vba 2007 .filesearch (Office 2007)

    Thanks, this is useful to know for anyone writing macros in Office 2007.

    More info about changes in Word 2007 VBA can be found in What's New, in particular elements that are now hidden in Object Model Changes Since Microsoft Office 2003.

    On the one hand, FileSearch had its share of problems, on the other hand, it made searching a folder and its subfolders very easy. You have to do more work if you want to search subfolders using FileSystemObject or Dir.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts