Monday, June 30, 2008

Search and Open Files using Excel VBA (FileSearch)

Sort Files By Date using Excel VBA File Search

Use the FileSearch property to return the FileSearch object. The following example searches for files and displays the number of files found and the name of each file.



Sub App_FileSearch_Example()

Dim OWB As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "c:\vbadud\delivered_files\"
.FileName = "*.xls"
If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderDescending) > 0 Then

For i1 = 1 To .FoundFiles.Count
Set OWB = Workbooks.Open(.FoundFiles(i1))
OWB.Save
OWB.Close
Next i1

End If

End With

End Sub

Use the NewSearch method to reset the search criteria to the default settings. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values. The following example resets the search criteria to the default settings before beginning a new search.

The above example will search for all excel files in a particular folder and opens the latest file. This method can also be used to Identify the last-modified or latest file using Excel VBA.

See also:

Open Excel Files - Open Dialog - GetOpenFilename Method

Browse a Folder / Select a Folder Thru Shell

OpenFileDialog in Visual Basic .Net

Visual Basic Common Dialog

11 comments:

  1. Anonymous5:48 PM

    The application.filesearch keeps returning an error. How do I make this work?

    ReplyDelete
  2. Anonymous6:24 PM

    Application.FileSearch will throw error if you are using Office 2007. You can use the following workaround :

    http://www.ozgrid.com/forum/showthread.php?t=71409

    ReplyDelete
  3. Anonymous1:05 AM

    Hi there...Can you post a code that can filter a data in table and returns its value in a notepad.Is it possible?

    ReplyDelete
  4. Anonymous6:44 AM

    If filesearch a property or object or both?

    ReplyDelete
  5. I need help!! I have tons of excel worksheets in a folder but I want to search and find them quicker and have them automatically open.

    ReplyDelete
  6. Hi, can excel look in more then one folder ?
    Let's say I give him a folder were to search, and then there are more folders to search in for the specified file.

    ReplyDelete
  7. Can you modify this VBA to search in more then one folder. Let's say I'll give him the path of the folder to search in, but there he will meet more folders to be searched.

    ReplyDelete
  8. Anonymous4:24 AM

    can we search all empty folders and files

    ReplyDelete
  9. can you modify the script to search word or string inside a files present in the folder

    ReplyDelete
  10. I need to sort thousands of excel files into folders based on a name found in the same tab and cell within each file. These need to be moved into a folder with a matching name to the string contents of that cell. Is there a way in vba to do this sorting process for me?

    ReplyDelete
  11. Anonymous9:51 AM

    Why don't all you people make an effort to figure these questions yourself. Just because he posted some useful info doesn't make him your lackey. Bunca lazy azzes

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group