Chapter 13: Manipulating Files
|
| < Day Day Up > |
|
Many applications that you develop for Microsoft Excel require working with multiple files. For example, you might need to get a listing of files in a directory, delete files, or rename files. Excel, of course, can import and export several types of text files. In many cases, however, Excel’s built-in text file handling is not sufficient. For example, you might need to import a text file that contains more than 256 columns of data, which is Excel’s limit, or the file might use a nonstandard delimiter such as a backward slash (\).
In this chapter, you’ll learn how to locate, write to, and read from external files. You’ll also learn to narrow your search using wildcards, as well as to search files for specific values.
Locating External Files
The Office Object Model is made available to all Microsoft Office applications, as discussed in previous chapters. It contains objects that are used by all Office applications, such as the CommandBars object, which is discussed in Chapter 17. You’ll learn how to utilize two objects contained in the Office Object Model that you use to search for files: FileSearch and FileDialog. You’ll also evaluate the following objects associated with each object.
The following files are associated with the FileSearch object:
-
FoundFiles
-
FileTypes
-
SearchScopes
-
ScopeFolders
-
SearchFolders
The following files are associated with the FileDialog object:
-
FileDialogFilters
-
FileDialogSelectedItems
The FileSearch object allows you to search for files with a wide range of search criteria. You are able to search by file type, file size, file location, and date of last modification. The FileSearch object places the names of the files it finds in the FoundFiles collection.
You can use the FileSearch object instead of the VBA Dir function for a range of file operations. FileSearch is useful for maintenance of files. For example, you can locate files of a certain age and delete them or move them to an archive directory. The FileSearch object is also useful when you need to retrieve data from a number of related files. For example, you can find all the Excel files in a certain directory that pertain to a new marketing initiative for The Garden Supply Company, before you consolidate the information into a summary file.
The FileDialog object was introduced in Office XP and enhanced with Office 2003. You are able to display the File Open and File Save As dialog boxes as well as a subdirectory browser. FileDialog is a more powerful version of the GetOpenFileName and GetSaveAsFileName methods of the Excel Application object, which are available in previous versions of Excel, but have not been available to other Office applications. FileDialog, being an Office object, is available to all Office applications.
Returning All Files
The FileSearch property is used to located file names based on your search criteria. It places the file names returned from the search in the FoundFiles collection. This object gives your code the functionality of the File Search feature available in the Excel application. For example, you can search for all Excel files by the file extension or search for files containing specific text. Table 13-1 lists some of the properties and methods used in the FileSearch object.
| Property or Method | Result |
|---|---|
| FileName | Searches for the name of the file specified. Wildcards can be used in the search criteria. |
| FoundFiles | Returns an object that contains the names of the files found. |
| LookIn | Specifies the directory to be searched. |
| SearchSubFolders | Sets the search to look in subfolders if True, or to ignore subfolders if set to False. |
| Execute | Initiates the search. |
| NewSearch | Clears previous results in the FileSearch object from previous searches. |
Consider the following example, in which the object variable FS is declared as part of the Office.FileSearch object. The Office prefix is not required, but this prefix makes it clear that the FileSearch object is an object in the Office library. In the code, the FileSearch property of the Excel Application object returns a reference to the FileSearch object and assigns it to the FS variable. Values are then assigned to a number of FileSearch properties. The LookIn property tells FileSearch which subdirectory to search. NewSearch is a method that clears all the FileSearch properties except LookIn. Because these properties are retained while Excel is open, it’s a good idea to execute NewSearch each time you use the FileSearch method. The SearchSubFolders property controls whether you look in subdirectories below the LookIn subdirectory.
| Note | Be sure to clear your previous search settings by executing the NewSearch method each time the FileSearch method is used. The FileSearch properties are retained in the Excel application during your current session in Excel.
Sub FindAccountingExcelFiles() |
The FileType property determines which file extensions will be included in the search criteria. The msoFileTypeExcelWorkbooks constant directs the search to include all the Excel file extensions: .xls, .xlt, .xlm, .xlc, and .xla. Table 13-2 lists the other constants available.
| msoFileType Constants | Value |
|---|---|
| msoFileTypeAllFiles | 1 |
| msoFileTypeOfficeFiles | 2 |
| msoFileTypeWordDocuments | 3 |
| msoFileTypeExcelWorkbooks | 4 |
| msoFileTypePowerPointPresentations | 5 |
| msoFileTypeBinders | 6 |
| msoFileTypeDatabases | 7 |
| msoFileTypeTemplates | 8 |
| msoFileTypeOutlookItems | 9 |
| msoFileTypeMailItem | 10 |
| msoFileTypeCalendarItem | 11 |
| msoFileTypeContactItem | 12 |
| msoFileTypeNoteItem | 13 |
| msoFileTypeJournalItem | 14 |
| msoFileTypeTaskItem | 15 |
| msoFileTypePhotoDrawFiles | 16 |
| msoFileTypeDataConnectionFiles | 17 |
| msoFileTypePublisherFiles | 18 |
| msoFileTypeProjectFiles | 19 |
| msoFileTypeDocumentImagingFiles | 20 |
| msoFileTypeVisioFiles | 21 |
| msoFileTypeDesignerFiles | 22 |
| msoFileTypeWebPages | 23 |
The LastModified property can use the following constants listed in Table 13-3.
| msoLastModified Constants | Value |
|---|---|
| msoLastModifiedYesterday | 1 |
| msoLastModifiedToday | 2 |
| msoLastModifiedLastWeek | 3 |
| msoLastModifiedThisWeek | 4 |
| msoLastModifiedLastMonth | 5 |
| msoLastModifiedThisMonth | 6 |
| msoLastModifiedAnyTime | 7 |
Instead of using the FileType property, you can specify the FileName property.
.FileName = "*.xls"
The FileName property allows you to narrow your search more than the FileType constant. If you use both FileType and FileName, the FileName property overrides the FileType property. You can also search the text contained in the properties of a file or in the body of the file itself by assigning the text to the TextOrProperty property of the FileSearch object.
The Execute method of the FileSearch object carries out the search and adds an object representing each file to the FoundFiles collection. The Execute method also returns a value that is the number of files found.
You use the FoundFiles collection to access the names, including the path, of the files found. The code from the previous example uses a For Each…Next loop to process the list, adding each name to strMessage, separated by a carriage return.
Limiting the File Search
There are times when you need to reopen a file from several months ago, but you don’t remember its exact location. We have all run into this problem at one time or another. The File Search feature in Excel can accomplish this task. However, you can use the PropertyTests collection in your VBA procedure to automate the search process.
For example, the Garden Supply Company has decided to reorganize their historical files. All files created in the previous year will be moved to an Archive folder on the company’s file server. The following procedure was designed to search for files that were modified in the previous month:
Sub FindLastMonthFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim strMessage As String
Dim i As Long
Dim iCount As Long
Set FS = Application.FileSearch
strPath = "C:\GSC\Accounting"
With FS
.NewSearch
.LookIn = strPath
.SearchSubFolders = True
With .PropertyTests
For i = .Count To 1 Step -1
.Remove i
Next i
.Add Name:="Files of Type", _
Condition:=msoConditionFileTypeExcelWorkbooks
End With
.LastModified = msoLastModifiedLastMonth
iCount = .Execute
strMessage = Format(iCount, "0 ""Files Found""")
For Each vaFileName In .FoundFiles
strMessage = strMessage & vbCr & vaFileName
Next vaFileName
MsgBox strMessage
End With
End Sub
The PropertyTests collection operates independently of any settings in the File Search task pane. This collection doesn’t recognize any conditions in the task pane and it doesn’t change the settings. If you add tests to the PropertyTests collection, they are retained until a NewSearch is executed.
The Add method of the PropertyTests collection adds the new tests, which are specified by assigning a string to the Name parameter that is identical to the string that appears in the Property combo box in the File Search task pane.
The FileType property for FileSearch can be used to limit your search to a specific file type or multiple file types. The FileTypes collection allows you to specify multiple file types. The following example will return all Microsoft Word and Excel files in the specified folders:
Sub FindWordandExcelFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim stMessage As String
Dim i As Long
Dim iCount As Long
Set FS = Application.FileSearch
strPath = "C:\GSC\Accounting"
With FS
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.FileTypes.Add msoFileTypeWordDocuments
.LookIn = strPath
.SearchSubFolders = True
.LastModified = msoLastModifiedAnyTime
iCount = .Execute
stMessage = Format(iCount, "0 ""Files Found""")
For Each vaFileName In .FoundFiles
stMessage = stMessage & vbCr & vaFileName
Next vaFileName
MsgBox stMessage
End With
End Sub
The FileTypes collection is retained until you execute NewSearch, which clears the collection and places the value of msoFileTypeOfficeFiles in the collection. However, there’s no need to empty the FileTypes collection before adding new entries. If you assign an entry to the FileType parameter, any existing entries in the FileTypes collection are destroyed and the new entry becomes the first and only entry in the collection. You can then use the Add method of the collection to add more entries. You can use the same type constants that were listed earlier in the chapter.
All the code that we have evaluated so far in this section assumes that you know the directory organization of the computer you are searching and can specify the subdirectories that you want to search. If you do not know the structure and need to map it yourself, a utility must be designed to search for files on any computer.
The SearchScopes collection provides a mechanism for carrying out the directory mapping process. The following example examines each member of the collection; each member is a SearchScope object.
Sub ListSearchScopeOptions()
Dim SS As SearchScope
Dim strMessage As String
For Each SS In Application.FileSearch.SearchScopes
strMessage = strMessage & SS.ScopeFolder.Name & vbTab
strMessage = strMessage & " Type=" & SS.Type & vbCr
Next SS
MsgBox strMessage
End Sub
The SearchScope objects represent the structures you can examine. The Type property identifies the category of each structure. The presence of My Computer and Network Places is no surprise. However, notice that Microsoft Outlook is given as another location. Your code should result in a dialog box similar to the one shown in Figure 13-1.
Figure 13-1: The ListSearchScopeOptions procedure result. Your result might vary slightly depending on how your computer is configured.
There are four SearchIn constants available. Table 13-4 lists the constants available.
| msoSearchIn Constants | Value |
|---|---|
| msoSearchInMyComputer | 0 |
| msoSearchInOutlook | 1 |
| msoSearchInMyNetworkPlaces | 2 |
| msoSearchInCustom | 3 |
The ScopeFolder property is available for each SearchScope object and references a ScopeFolder object. The ScopeFolder represents the top of the structure in the ScopeFolders collection that contains more ScopeFolder objects. The following example displays the Name and Path properties of the ScopeFolders under the top-level ScopeFolder of each structure:
Sub ListScopeFolderObjects()
Dim SS As SearchScope
Dim SF As ScopeFolder
Dim strMessage As String
Application.FileSearch.RefreshScopes
For Each SS In Application.FileSearch.SearchScopes
Select Case SS.Type
Case msoSearchInMyComputer
strMessage = SS.ScopeFolder.Name & vbCr
For Each SF In SS.ScopeFolder.ScopeFolders
strMessage = strMessage & SF.Name & vbTab & vbTab
strMessage = strMessage & "Path = " & SF.Path & vbCr
Next SF
Case msoSearchInMyNetworkPlaces
strMessage = strMessage & vbCr & SS.ScopeFolder.Name & vbCr
For Each SF In SS.ScopeFolder.ScopeFolders
strMessage = strMessage & SF.Name & vbTab
strMessage = strMessage & "Path = " & SF.Path & vbCr
Next SF
Case msoSearchInOutlook
strMessage = strMessage & vbCr & SS.ScopeFolder.Name & vbCr
For Each SF In SS.ScopeFolder.ScopeFolders
strMessage = strMessage & SF.Name & vbTab & vbTab
strMessage = strMessage & "Path = " & SF.Path & vbCr
Next SF
Case Else
strMessage = strMessage & vbCr & "Unknown SearchScope object"
End Select
Next SS
MsgBox strMessage
End Sub
The code will return a result similar to Figure 13-2.
Figure 13-2: Here is the ListScopeFolderObjects procedure result. Your result might vary depending on how your computer is configured.
The Select Case statements were used to isolate and examine each of the top-level structures. Each top-level ScopeFolders collection contains ScopeFolder objects that represent the root directories of the file structures available to it. Each of these ScopeFolder objects contains another ScopeFolders collection that represents the subdirectories under it. This provides a way to navigate through the directory trees.
The SearchFolders collection defines additional directory paths to be searched by adding ScopeFolder objects to the collection. SearchFolders is not like the FileTypes collection that is recreated when you assign a value to the FileType property. SearchFolders isn’t affected when you assign a value to the LookIn property or when you execute NewSearch. The LookIn value is additional to the SearchFolders entries.
The SearchFolders collection is also retained in the current Excel session, so empty the collection before executing a new search. Remember to empty the SearchFolders collection by looping through each ScopeFolder object and removing it. Review the following example to get a better understanding of how to search through all subdirectories in the root directory of the C drive. When it finds a directory starting with the characters Product, it will add the directory to the SearchFolders collection.
Sub SetupSearchFoldersCollection()
Dim FS As FileSearch
Dim SS As SearchScope
Dim SF As ScopeFolder
Dim sfSubFolder As ScopeFolder
Dim strMessage As String
Dim i As Long
Set FS = Application.FileSearch
For i = FS.SearchFolders.Count To 1 Step -1
FS.SearchFolders.Remove i
Next i
For Each SS In FS.SearchScopes
Select Case SS.Type
Case msoSearchInMyComputer
For Each SF In SS.ScopeFolder.ScopeFolders
Select Case SF.Path
Case "C:\"
For Each sfSubFolder In SF.ScopeFolders
If UCase(Left(sfSubFolder.Name, 6)) = _
"PRODUCT" Then
sfSubFolder.AddToSearchFolders
End If
Next sfSubFolder
Exit For
End Select
Next SF
Exit For
End Select
Next SS
Search_SearchFolders
End Sub
The example empties the SearchFolders collection and then searches through the SearchScopes and ScopeFolders collections to locate the C drive. The code then evaluates the Name property of each ScopeFolder in the root directory of the C drive to determine if the name begins with Product. Since the comparison of text is case sensitive, the Name property is converted to uppercase.
The previous example is dependent on the Search_SearchFolders procedure. When the code finds a matching directory, it uses the AddToSearchFolders method of the ScopeFolder object to add the object to the SearchFolders collection. The Search_SearchFolders routine is listed here:
Sub Search_SearchFolders()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim strMessage As String
Dim iCount As Long
Set FS = Application.FileSearch
With FS
.NewSearch
.LookIn = "c:\"
.SearchSubFolders = True
.Filename = "*.xls"
.LastModified = msoLastModifiedAnyTime
iCount = .Execute
strMessage = Format(iCount, "0 ""Files Found""")
For Each vaFileName In .FoundFiles
strMessage = strMessage & vbCr & vaFileName
Next vaFileName
MsgBox strMessage
End With
End Sub
The Search_SearchFolders collection sets the LookIn property of FileSearch to the C drive to ensure that it doesn’t contain any directory references from previous FileSearch operations.
 |
Determining if a File Exists Using FileSearch
There might be times when your procedure won’t run successfully without a particular file. You can simply use a function to determine if that file exists. For example, the following function was written to verify whether a file and its path are valid. The function will return True if the file exists and False if it was not found.
Function FileExists(path, fname) As Boolean
With Application.FileSearch
.NewSearch
.filename = fname
.LookIn = path
.Execute
If .FoundFiles.Count = 1 Then
FileExists = True
Else
FileExists = False
End If
End With
End Function
|
Finding Files with the FileDialog Dialog Box
The FileDialog object allows you to display the Open and Save As dialog boxes using VBA code. The GetOpenFileName and GetSaveAsFileName methods of the Application object achieve similar results and can be used for backward compatibility. However, the FileDialog object is available to all Office applications and is a familiar interface to the users.
In the following example, The Garden Supply Company has a list of pictures used in their spring catalog. You have been asked to create a worksheet that allows the user to choose the images and insert a preview into the worksheet.
| Important | The following two procedures assume there is a worksheet named ImagePreview in the active workbook.
Private Sub cmdGetFile_Click() |
The FileDialog property of the Application object returns a reference to the Office FileDialogs object. Table 13-5 lists the msofileDialogType constants available to specify the type of dialog.
| msoFileDialog Constants | Value |
|---|---|
| msoFileDialogOpen | 1 |
| msoFileDialogSaveAs | 2 |
| msoFileDialogFilePicker | 3 |
| msoFileDialogFolderPicker | 4 |
The Filters property of the FileDialog object returns a reference to the FileDialogFilters collection for the FileDialog. The filters control the types of files that are displayed. By default, there are 24 preset filters that the user can select from the drop-down list at the bottom of the File Open dialog box. The Clear method of the FileDialogFilters collection removes the preset filters, and we add our own filter that shows only .jpg files.
The Show method of the FileDialog object displays the dialog box. When the user clicks the Open button, the Show method returns a value of True. If the user clicks the Cancel button, the Show method returns False and you’ll exit the procedure.
The Show method does not actually open the selected file, but instead places the file name and path into the FileDialogSelectedItems collection. It’s possible to set this property to allow users to select multiple files. By default, the name of the file is returned from the first item in the FileDialogSelectedItems collection, which is referred to by the SelectedItems property of the FileDialog object.
There are few differences between the four possible dialog types apart from the heading on the title bar. The file picker and folder picker types show Browse in the title bar, and the others show File Open and File Save As, as appropriate. All the dialogs show the folders and files except the folder picker dialog, which shows only folders.
As you have seen, the Show method displays the FileDialog, and the items chosen are placed in the FileDialogSelectedItems object without any attempt to open or save any files. The following example shows how you can use the Execute method with the File Open and Save As dialogs to carry out the required Open or Save As operations immediately when the user clicks the Open or Save button.
With Application.FileDialog(xlDialogOpen)
If .Show Then .Execute
End With
Consider the following example. The cmdShowProductImage_Click procedure has been modified to allow the user to select multiple files by holding down the Shift or Ctrl keys while clicking on the file names. The file names are then loaded into the List box named lstFileList, allowing the user to display the files by selecting the file name.
Private Sub cmdShowProductImage_Click()
Dim FD As FileDialog
Dim FFs As FileDialogFilters
Dim strFileName As String
Dim vaItem
Dim intCounter As Integer
On Error GoTo Problem
Set FD = Application.FileDialog(msoFileDialogOpen)
With FD
Set FFs = .Filters
With FFs
.Clear
.Add "Pictures", "*.jpg"
End With
.AllowMultiSelect = True
If .Show = False Then Exit Sub
intCounter = 1
For Each vaItem In .SelectedItems
Worksheets("ImagePreview").Pictures.Insert _
(.SelectedItems(intCounter))
intCounter = intCounter + 1
Next vaItem
End With
Exit Sub
Problem:
MsgBox "You have not selected a valid picture."
End Sub
The AllowMultiSelect property is set to True, allowing the user to select multiple files. The list box is cleared of any previous entries, and the For…Each Loop adds the items into the FileDialogSelectedItems collection. The ListIndex property is set to 0 each time the user selects a new file, and then the Change event procedure is executed loading the new image.
|
Determining if a File Exists Using FileDialog
Previously in this chapter, you saw how to determine if a file existed using the FileSearch object. Now that you have reviewed the FileDialog object, review the following function. The same results are returned, but a different method is used to achieve the same results. The function will return True if the file exists and False if it isn’t found.
Function FileExists2(fname) As Boolean
Set FileSys = CreateObject("Scripting.FileSystemObject")
FileExists2 = FileSys.FileExists(fname)
End Function
The function created an instance of the FileSystemObject object. The FileSystemObject gives you access to the computer’s file system. Once access is granted to the computer’s file system, the function uses the FileExists property of the FileSys object to determine if the file exists. The code is much simpler and more elegant than the earlier example.
|
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161