FileTypes Object - Microsoft Office Excel

Remarks

There is only one FileTypes collection for all searches so it's important to clear the FileTypes collection before executing a search unless you wish to search for file types from previous searches. The easiest way to clear the collection is to set the FileType property to the first file type for which you want to search. You can also remove individual types using the Remove method. To determine the file type of each item in the collection, use the Item method to return the msoFileType value.

Note
The FileType property of the FileSearch object clears the FileTypes collection and sets the first item in the collection to the file type defined by the FileType property.

Example

Use the FileTypes property with the FileSearch object to return a FileTypes collection; for example:

Visual Basic for Applications
`Set ft = Application.FileSearch.FileTypes`

The following example searches for all HTML and Microsoft Excel files on the C:\ drive.

Visual Basic for Applications

Visual Basic for Applications
Sub SearchForFiles() 'Declare a variable to act as a generic counter Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Setting the FileType property clears the 'FileTypes collection and sets the first 'item in the collection to the file type 'defined by the FileType property. .FileType = msoFileTypeWebPages 'Add a second item to the FileTypes collection .FileTypes.Add msoFileTypeExcelWorkbooks 'Display the number of FileTypes in the collection. MsgBox "You are about to search for " & .FileTypes.Count & _ " file types." 'Set up the search to look in all subfolders on the C:\ drive. .LookIn = "C:\" .SearchSubFolders = True 'Execute the search and test to see if any files 'were found. If .Execute > 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount Else MsgBox "No files found." End If End With End Sub

Sub SearchForFiles() 'Declare a variable to act as a generic counter Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Setting the FileType property clears the 'FileTypes collection and sets the first 'item in the collection to the file type 'defined by the FileType property. .FileType = msoFileTypeWebPages 'Add a second item to the FileTypes collection .FileTypes.Add msoFileTypeExcelWorkbooks 'Display the number of FileTypes in the collection. MsgBox "You are about to search for " & .FileTypes.Count & _ " file types." 'Set up the search to look in all subfolders on the C:\ drive. .LookIn = "C:\" .SearchSubFolders = True 'Execute the search and test to see if any files 'were found. If .Execute > 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount Else MsgBox "No files found." End If End With End Sub

The following example loops through the FileTypes collection and removes any file types that aren't Microsoft Word or Microsoft Excel files (in general, it's simpler to clear the FileTypes collection and start from scratch).

Visual Basic for Applications

Visual Basic for Applications
Sub RemoveFileTypeFromCollection() 'Define an integer to use as a counter 'when iterating through the FileTypes collection. Dim intFileIndex As Integer 'Use a With...End With block to reference the FileSearch object. With Application.FileSearch 'Loop through all of the items in the FileTypes collection. intFileIndex = 1 Do While intFileIndex = .FileTypes.Count Select Case .FileTypes.Item(intFileIndex) Case msoFileTypeWordDocuments, msoFileTypeExcelWorkbooks Case Else 'If the file type isn't a Microsoft Word or 'Microsoft Excel file, remove it. .FileTypes.Remove intFileIndex 'Decrement the counter so that no file types are missed. intFileIndex = intFileIndex - 1 End Select 'Increment the counter to test the next file type. intFileIndex = intFileIndex + 1 Loop End With End Sub

Sub RemoveFileTypeFromCollection() 'Define an integer to use as a counter 'when iterating through the FileTypes collection. Dim intFileIndex As Integer 'Use a With...End With block to reference the FileSearch object. With Application.FileSearch 'Loop through all of the items in the FileTypes collection. intFileIndex = 1 Do While intFileIndex = .FileTypes.Count Select Case .FileTypes.Item(intFileIndex) Case msoFileTypeWordDocuments, msoFileTypeExcelWorkbooks Case Else 'If the file type isn't a Microsoft Word or 'Microsoft Excel file, remove it. .FileTypes.Remove intFileIndex 'Decrement the counter so that no file types are missed. intFileIndex = intFileIndex - 1 End Select 'Increment the counter to test the next file type. intFileIndex = intFileIndex + 1 Loop End With End Sub

See also: