|
|
You can use the FileSearch object model (shown in Figure 8-2) and other selected technologies to integrate file searches into your applications. With Access 2003, the model offers easy access to a broad range of search scopes, including your computer's hard drives, the publicly shared resources on the local area network (LAN) that a computer connects to, Outlook items, and Web files. This object exposes the functionality of the Open and the Find dialog boxes. As Figure 8-2 shows, five collections are dependent on the FileSearch object. In addition to the FoundFiles and the PropertyTests collections from Access 2002, Access 2003 adds the FileTypes, SearchFolders, and SearchScopes collections for the FileSearch object.
| Note |
Access Help contains code samples that illustrate how to loop through SearchScopes to find SearchFolders and, ultimately, paths within those folders in which you want to search for files. One major benefit of SearchScopes is its ability to search for files that are not on your computer or LAN, such as those located in Web folders. See the "Printing Web Folder Contents" section in Chapter 2 for code samples using the ADODB library that itemize the files on a Web folder. |
There are two basic ways to specify a file search, and each approach corresponds to options in the Open dialog box:
You can designate a single criterion (a filename or pattern, a file type, or a path).
You can designate multiple search criteria programmatically by using the PropertyTests collection.
With the second approach, you specify arguments that serve as input to the Open dialog box. You can use this dialog box to specify multiple search criteria and rules for concatenating them, such as And and Or operators. Use FoundFiles to enumerate the result set from either approach. I use the term result set because the collection of found files corresponds roughly to the rows of a result set from a SELECT statement.
The FileSearch object has three methods: NewSearch, Execute, and RefreshScopes. The NewSearch method resets all FileSearch properties to their default values. You can then edit the properties that require special values for a particular file search. If you do not invoke NewSearch at the beginning of a search specification, your new search inherits its property settings from the previous search.
You invoke the Execute method to launch a file search after you specify the search parameters. This method can take several arguments that control the arrangement of filenames in the FoundFiles object and that control whether to update the file index before conducting a new search. The return value from this method is the number of filenames that match the search specification.
The RefreshScopes method updates the ScopeFolders collection to reflect the addition or removal of folders since the instantiation of the FileSearch object. This method complements the NewSearch method as a means of reusing an existing FileSearch object. You can programmatically add and drop folders with the MkDir and RmDir methods for the FileSystem class in the VBA library; these methods are available to you as built-in Access functions. Consequently, the RefreshScopes method can determine the range of folders in which the FileSearch object conducts a search.
Many FileSearch properties permit flexible search specifications. The simple code sample that follows specifies a search and retrieves its result set. It creates an instance of the FileSearch object by using the FileSearch property of the Application object. Then it restores all FileSearch property settings to their default values by invoking the NewSearch method. Next, it assigns the LookIn and FileName properties, which specify where to look and what to look for. The test machine for this search includes a series of .mdb files with names such as Chapter01 and Chapter02.
The SearchSubFolders property accepts a Boolean value that indicates whether to restrict the search to the current folder or extend it to subfolders of the LookIn property setting. In this instance, the setting matters because the BooksArchive\Access11 folder on the cab2000 computer in my office has a subdirectory for each book chapter. When dealing with folders that can contain many subfolders (such as C:\), you should be careful about how you set the SearchSubFolders property because it can lead to some lengthy searches.
Sub FileSearch1XP() 'Search in the BooksArchive\Access11 path and 'its subfolders on the c drive of the cab2000 'computer for Chapter*.mdb With Application.FileSearch 'Start a new search .NewSearch 'Set search criteria .LookIn = "\\cab2000\c\BooksArchive\Access11" .FileName = "Chapter*.mdb" .SearchSubFolders = True End With With Application.FileSearch 'Execute the search If .Execute() > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." 'Display names of all found files For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else 'If no files found, say so MsgBox "There were no files found." End If End With End Sub
After creating the specification for the search, the procedure invokes the Execute method for the FileSearch object. This method has a return value that indicates the number of files that meet the search criteria. If the value is 0, the criteria yield no matching filenames and the procedure issues a message indicating that no files were found. If the criteria yield one or more matching files, the procedure displays the Count property of the FoundFiles object before presenting each name in FoundFiles.
The preceding sample enumerates all the database files from the target folder specified by the LookIn setting that have an .mdb extension and start with "Chapter". You can easily broaden the scope of the search to include all database files no matter how their filename starts and regardless of whether they have an .mdb extension. For example, the next piece of code searches for database files in the same folder as the preceding sample. But this code sample retrieves database files with other extensions, such as .adp, .mde, and .ade. Also, the sample places no restrictions on the filename. Therefore, it can find a database filename even if it does not begin with "Chapter".
Sub FileSearch1aXP() 'Search in the target path and its subfolders 'for database files With Application.FileSearch 'Start a new search .NewSearch 'Set search criteria .LookIn = "\\cab2000\c\BooksArchive\Access11" .FileType = msoFileTypeDatabases .SearchSubFolders = True End With With Application.FileSearch 'Execute the search If .Execute() > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." 'Display names of all found files For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else 'If no files found, say so MsgBox "There were no files found." End If End With End Sub
The following sample sorts the result set from a search by file size. The sample uses two procedures. The first procedure specifies a computer name, a share name, and a pathname. These three parameters can jointly designate the LookIn setting for a search. By specifying the parameters in a procedure that passes them to another, we make the procedure that invokes the Execute method for the FileSearch object more general.
The second procedure accepts the arguments passed to it by the first procedure, and it concatenates them to compute a value for the LookIn property of the FileSearch object. In addition, the second procedure uses two more parameters when it invokes the Execute method. These parameters designate the sort criterion and order, respectively, for arranging the result set from the search. The constant names for the Execute method's first parameter indicate the variable on which to sort the returned filenames. These constants are msoSortByFileName, msoSortByFileType, msoSortByLastModified, and msoSortBySize. The Execute method's second parameter specifies either ascending or descending order. The sample designates a search sorted by file size in descending order. This differs from the previous sample, which returned results in the default ascending order based on filename.
Sub CallFileSearch2XP() Dim str1 As String Dim str2 As String Dim str3 As String 'Dim ftp1 As fil str1 = "\\cab2000\" str2 = "c\" str3 = "BooksArchive\Access11" FileSearch2XP str1, str2, str3 End Sub Sub FileSearch2XP(ComputerName As String, _ ShareName As String, PathName As String, _ Optional FileType) Dim sngMB As Single 'Search in folder specified by arguments and its 'subfolders for *.mdb With Application.FileSearch 'Start a new search .NewSearch 'Set search criteria .LookIn = ComputerName & ShareName & PathName .FileName = "*.mdb" .SearchSubFolders = True End With With Application.FileSearch 'Return found files in descending order by file size If .Execute(msoSortBySize, msoSortOrderDescending) > 0 Then Debug.Print "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count 'Compute file size in MB and display with filename sngMB = FileLen(.FoundFiles(i)) / (1024 ^ 2) Debug.Print .FoundFiles(i) & vbCrLf & vbTab & _ "Filesize (MB): " & Round(CDec(sngMB), 3) Next i Else 'If no files found, say so MsgBox "There were no files found." End If End With End Sub
The sample prints the result set to the Immediate window to show the file sizes and filenames. The code demonstrates the syntax for passing the FoundFiles object to the FileLen function to determine the file size in bytes. The division by 1024^2 converts the function's return value from bytes to megabytes (MB). Then, by applying the Round function, the procedure represents the value to the nearest 1/1000 of a megabyte.
| Note |
VBA 6 introduced the VBA Round function. To derive consistent results with this function, you should first pass its argument to the CDec function. The sample just shown uses this syntax. |
Even with a simple search, as shown in the three previous samples, you can selectively search for specific text, such as ADO, in the document or its DocumentProperty object. The sample that follows does this. You use the FileSearch object's TextOrProperty property to target a text string in the file's body or its Properties collection. Notice that you can specify folders on remote computers using the Universal Naming Convention (UNC). There is nothing new about this capability, except now we exercise it to search for text within the files on another computer. This sample uses the same name and path as the preceding samples. (The path \\cab2000\c\BooksArchive\Access11 points to the BooksArchive\Access11 folder in the share named c of a computer named cab2000.) As with other samples in this book, you'll need to change these settings so that they point to the computers, shares, and paths that are relevant to your computing environment.
Sub FileSearch3XP() Dim sngStart As Double Dim sngEnd As Double Dim int1 As Integer Dim str1 As String #Const AllFiles = False 'Search in a folder on linked computer 'for files containing ADO With Application.FileSearch 'Start a new search .NewSearch 'Set search criteria .LookIn = "\\cab2000\c\BooksArchive\Access11" .SearchSubFolders = False 'When searching for text, consider 'restricting the files you search '*.* can require more time 'than msoFileTypeWordDocuments #If AllFiles = True Then .FileName = "*.*" #Else .FileType = msoFileTypeWordDocuments #End If .TextOrProperty = "ADO" End With With Application.FileSearch 'Execute the search sngStart = Now If .Execute() > 0 Then sngEnd = Now Debug.Print "The file search took " & _ DateDiff("s", sngStart, sngEnd) & " seconds." str1 = "There were " & .FoundFiles.Count & _ " file(s) found. Do you want to see " & _ "them in a series of messages boxes?" If MsgBox(str1, vbYesNo, _ "Programming Microsoft Access 2003") = vbYes Then 'Display names of all found files For int1 = 1 To .FoundFiles.Count MsgBox .FoundFiles(int1) Next int1 End If Else 'If no files found, say so MsgBox "There were no files found." End If End With End Sub
Some file searches are lengthy. By restricting the scope of a FileSearch object, you can dramatically improve the performance of the Execute method. Three settings that affect search performance include the Lookin, SearchSubFolders, and FileTypes properties. A good optimizing rule is to look at the lowest level folder, avoid searching subdirectories, and search for a specific file type. For example, the sample just shown finds all Word documents in a folder that contain a specific string—namely, ADO. On the LAN in my office, this search takes about three seconds. By changing the search settings so the Execute method looks at all files (AllFiles = True) from the root directory on the c share (Lookin = \\cab2000\c) and all its subdirectories (SearchSubFolders = True), the search time grows to 10,370 seconds! I got a nice break while the computer timed this search. The two properties that most affect search performance in this example are Lookin and SearchSubFolders. However, you also can attain meaningful performance gains by using the FileType property to search for a specific kind of file (FileType = msoFileTypeWordDocuments) instead of searching for all files (FileName = *.*) in a search path. On the LAN in my office, using a modified version of the preceding sample to search just for Word documents is 14 seconds faster than searching all documents in the BooksArchive folder.
In general, your applications will offer file search capabilities through a form. The next sample demonstrates one technique for this while showing how to condition a search on one or more members of the FileTypes collection for the FileSearch object. The sample's form, which appears in Figure 8-3, allows a user to perform one of three tasks. First, users can specify a new search by designating a single file type for it. The combo box on the form shows the list of possible FileType designations. Second, users can add a new member to the FileTypes collection for the current FileSearch object. Again, users must specify which FileType to add via the combo box. Third, they can execute the search with the FileTypes collection members showing in the form's list box. This last capability depends, in part, on a procedure named FileSearch1a3aXP in a standard module. A module behind the form supports all the form's capabilities, including the call to the procedure in the standard module.
The following listing includes all the event procedures and sub procedures behind the form. These procedures provide two classes of services. The first class manages the form by performing tasks such as managing the entries that appear in the list box and responding to command button clicks. The second class of services manages the search. This involves tasks such as managing the members of the FileTypes collection for the open FileSearch object and launching the search with whatever members are in the list.
The form module starts by declaring three public variables. These variables simplify sharing information across procedures (namely, the settings for the FileSearch object). These initial declarations also reduce the need to repetitively declare variables used in standard ways across procedures.
The first procedure is the Form_Open event procedure. This procedure handles the formatting and content settings for the form. It also sets a reference to the FileSearch object and uses that reference to populate the form's list box with members of the object's FileTypes collection. Furthermore, the procedure uses the AddItem and RemoveItem methods to control the content of the list box. These methods require a list box control to have a Value List setting for its RowSourceType property.
| Note |
Access 2002 introduced the AddItem and RemoveItem methods for list boxes and combo boxes. The code behind the form in Figure 8-3 illustrates how to use them. Refer to Access Help for online programmer reference materials. Chapter 5 has a sample illustrating use of the AddItem method. |
The Form_Open event procedure has two loops. A backward-iterating loop that successively invokes the RemoveItem method erases any previously existing content from the list box. A forward-iterating loop passes through the members of the FileTypes collection. This loop uses the AddItem method to add the names of the members to the list box. Office stores members of the FileTypes collection as intrinsic constant values (numbers). A lookup table (msoFileTypeEnum) in the Chapter08 .mdb file maps the numbers to their intrinsic constant names. A DLookup function within the loop operates on the table to extract the intrinsic constant name corresponding to the number for a FileType collection member. Notice in the code that FileTypes collection members belong to a 1-based collection, but the rows in a list box belong to a 0-based collection.
The cmdDesignateSingle_Click and cmdAddTo_Click event procedures work in concert with the RefreshWithNewFileTypeinFileSearch procedure to keep the list box control rows in synchronicity with the members of the FileTypes collection. The two procedures also populate the FileTypes collection with members. The event procedure for clicking the cmdDesignateSingle button starts by assigning the combo box's value to the FileTypes collection for the FileSearch object referenced in the Form_Open event procedure. The assignment statement clears any prior members of the FileTypes collection as it adds the combo box's value to the collection. Next, it removes all row values from the list box in a backward-iterating loop. The call to the RefreshWithNewFileTypeinFileSearch procedure passes the last member from the FileTypes collection to the list box. In order for the cmdDesignateSingle_Click procedure to work properly, users must first make a selection from the combo box. An error trap (NullSingleTrap), catches cases where users fail to make a selection and reminds them with a message box.
The cmdAddTo_Click event procedure has the same general design as the cmdDesignateSingle_Click event procedure, but cmdAddTo_Click merely adds an item from the combo box to the FileTypes collection and the list box rows. Instead of using an assignment statement to transfer the combo box value to the FileTypes collection, the cmdAddTo_Click procedure uses the collection's Add method. This method inserts the combo box's value as the last member of the collection without affecting any other members of the collection. Again, there's an error trap in case users forget to make a selection from the combo box before clicking the button. Let's take a look at the syntax now.
Public fls1 As FileSearch
Public int1 As Integer
Public str1 As String
Private Sub Form_Open(Cancel As Integer)
'Set RowSourceType to Value List so you can use
'AddItem and RemoveItem methods
Me.lstExistingFileTypes.RowSourceType = "Value List"
'Set form for unbound data use
Me.RecordSelectors = False
Me.NavigationButtons = False
Me.DividingLines = False
'Remove all existing items from the list box
'showing selected file types
For int1 = Me.lstExistingFileTypes.ListCount - 1 To 0 Step -1
Me.lstExistingFileTypes.RemoveItem int1
Next int1
'Set FileSearch variable
Set fls1 = Application.FileSearch
'Loop through FileTypes in FileSearch object and
'add them to the list box
'FileTypes collection is 1-based and list box items
'are 0-based
For int1 = 1 To fls1.FileTypes.Count
str1 = "[msoFileTypeNumber] = " & _
CStr(fls1.FileTypes.Item(int1))
Me.lstExistingFileTypes.AddItem _
Item:=DLookup("[msoFileTypeName]", _
"msoFileTypeEnum", str1), _
Index:=Me.lstExistingFileTypes.ListCount
Next int1
End Sub
Private Sub cmdDesignateSingle_Click()
On Error GoTo NullSingleTrap
'Refresh the FileTypes collection of the FileSearch
'object with the combo box selection
fls1.FileType = Combo0
'Remove all existing items from the list box
'showing selected file types
For int1 = Me.lstExistingFileTypes.ListCount - 1 To 0 Step -1
Me.lstExistingFileTypes.RemoveItem int1
Next int1
'Refresh the list box with the newly selected item
RefreshWithNewFileTypeinFileSearch
'Blank combo after processing selection
Combo0 = ""
NormalExit:
Exit Sub
NullSingleTrap:
If Err.Number = 94 Then
MsgBox "Select a file type from the combo " & _
"box before clicking the button.", vbCritical, _
"Programming Microsoft Access 2003"
Else
MsgBox "Call Support to report this problem.", _
vbCritical, "Programming Microsoft Access 2003"
End If
End Sub
Private Sub cmdAddTo_Click()
On Error GoTo NullAddTrap
'Add the combo box selection to the existing list
'of members in the FileTypes collection of the
'FileSearch object
fls1.FileTypes.Add Combo0
'Refresh the list box with the newly selected item
RefreshWithNewFileTypeinFileSearch
'Blank combo after processing selection
Combo0 = ""
NormalExit:
Exit Sub
NullAddTrap:
If Err.Number = 94 Then
MsgBox "Select a file type from the combo " & _
"box before clicking the button.", vbCritical, _
"Programming Microsoft Access 2003"
Else
MsgBox "Call Support to report this problem.", _
vbCritical, "Programming Microsoft Access 2003"
End If
End Sub
Sub RefreshWithNewFileTypeinFileSearch()
'Dim int1 As Integer
'Dim str1 As String
'Find the index for the last item in the FilesTypes collection
int1 = fls1.FileTypes.Count
str1 = "[msoFileTypeNumber] = " & CStr(fls1.FileTypes.Item(int1))
'Add name corresponding to the FileType number to the bottom
'of the 1ist box
Me.lstExistingFileTypes.AddItem _
Item:=DLookup("[msoFileTypeName]", "msoFileTypeEnum", str1), _
Index:=Me.lstExistingFileTypes.ListCount
End Sub
Private Sub cmdSearch_Click()
'Launch search with setting from form
Module1.FileSearch1a3aXP
End Sub
The last event procedure, cmdSearch_Click, in the code behind the form consists of a single line. This line invokes the FileSearch1a3aXP procedure in Module1, which is a standard module. The FileSearch1a3aXP procedure contains elements of the two preceding search procedures in the module whose names end with 1aXP and 3aXP. The actual code appears next. One distinction between this procedure and the ones we examined earlier in the section is that it doesn't invoke the New method for the FileSearch object. This is because the procedure needs to retain the FileTypes collection members set by the form.
Sub FileSearch1a3aXP() 'Search in Program Files folder and its subfolders With Application.FileSearch 'Start a new search. 'Set search criteria. .LookIn = "C:\PMA Samples" .SearchSubFolders = True End With With Application.FileSearch 'Execute the search If .Execute() > 0 Then str1 = "There were " & .FoundFiles.Count & _ " file(s) found." MsgBox str1, vbInformation, _ "Programming Microsoft Access 2003" Else 'If no files found, say so MsgBox "There were no files found.", vbInformation, _ "Programming Microsoft Access 2003" End If End With End Sub
The advanced search format lets you specify multiple search criteria for your result set in the FoundFiles collection. You use the Add method two or more times to specify multiple criteria for the PropertyTests collection. Your individual criterion specifications must include Name and Condition settings.
The Add method can specify a Connector setting as well as one or two Value settings. The Add method's Condition setting determines whether a criterion requires Value settings. You can view the members of the MsoCondition class in the Object Browser to see all the available options. Figure 8-4 shows an excerpt. Your Connector settings can take one of two values to specify how to combine a criterion with other criteria. This setting enables And or Or operators for merging a criterion with other search criteria. You use Or to treat the criterion separately, and you use And to combine the designated criterion with others. The And operator is the default setting. Together, the Condition, Value, and Connector settings offer the same functionality as the Find dialog box.
You can enumerate PropertyTests members using a For…Each loop. Each member constitutes a unique search criterion. The Name property identifies the criterion as you enumerate them.
The file search sample that follows has three segments. The first segment specifies the criteria after setting a reference to a FileSearch object. The sample targets all Web files between two dates where the files are located in the \Program Files\Microsoft Office\Office11\Samples folder of the C drive on the current computer. The code shows the correct syntax for invoking the Add method for the PropertyTests collection. The first criterion designates a Web page file type. The second criterion denotes files last modified between January 1, 2002 and December 31, 2003. The msoConnectorOr setting indicates that files must meet both criteria separately to be in the result set. You need not specify a Connector property for the second criterion because it adopts the default msoConnectorAnd value. Before displaying the result set, the procedure enumerates the PropertyTests members in its second segment. The final segment displays the result set.
Sub FileSearch4XP() Dim fls1 As FileSearch Dim str1 As String Dim int1 As Integer Set fls1 = Application.FileSearch 'Set LookIn and SearchSubFolder properties With fls1 .NewSearch .LookIn = "\\cab2000\c\Program Files\" & _ "Microsoft Office\Office10\Samples" .SearchSubFolders = False End With 'Remove default PropertyTest for Office files fls1.PropertyTests.Remove (1) 'Set a pair of PropertyTests to find all Web pages 'last modified on either 10/30/2000 or 10/31/2000 With fls1.PropertyTests .Add Name:="Files of Type", _ Condition:=msoConditionFileTypeWebPages, _ Connector:=msoConnectorOr .Add Name:="Last Modified", _ Condition:=msoConditionAnytimeBetween, _ Value:="11/1/2002", SecondValue:="12/31/2003" End With 'Display PropertyTests For int1 = 1 To fls1.PropertyTests.Count With Application.FileSearch.PropertyTests(int1) str1 = "This is the search criteria: " & vbCrLf & _ "The name is: " & .Name & ". " & vbCrLf & _ "The condition is: " & .Condition If .Value <> "" Then str1 = str1 & "." & vbCrLf & "The value is: " & .Value If .SecondValue <> "" Then str1 = str1 _ & ". " & vbCrLf & "The second value is: " _ & .SecondValue & ", and the connector is " _ & .Connector End If End If MsgBox str1 End With Next int1 'Display return set from property tests With fls1 'Execute the search If .Execute() > 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." 'Display names of all found files For int1 = 1 To .FoundFiles.Count MsgBox .FoundFiles(int1) Next int1 Else 'If no files found, say so MsgBox "There were no files found." End If End With End Sub
One reason for finding files is to copy them to another destination or save them with a new name. The FileSearch object does not facilitate either task, but the FileSystemObject object does. This object provides a general model for processing text and binary files. The CopyFileUtility procedure described in this section illustrates a simple application of the FileSystemObject object that I hope will entice you to explore FileSystemObject more fully when your applications require file processing. You can learn more about this tool by searching for FileSystemObject in VBScrip5.chm. Perform a search on your computer of the Program Files folder to locate the exact path to the file. The FileSystemObject object is an exceedingly robust tool for many Office applications.
The following code sample uses two procedures to manage the operation of the CopyFile method. The first procedure sets the computer and share names, the pathname, and the filename for the original file and the copy that the utility attempts to generate. This procedure also sets a Boolean variable to determine whether the utility will copy over a file that already exists. The second procedure instantiates a reference to FileSystemObject with the CreateObject function. The CreateObject function is explored in more depth in Chapter 9, and Chapter 1 includes a brief description of its use with FileSystemObject. After instantiating the object, the procedure compiles the source and destination arguments for the object's CopyFile method.
Users of an application must have appropriate permissions to an original file and the location of a destination file for the CopyFile method to function correctly. The most recent Microsoft Windows operating systems, such as Windows XP, do not enable these permissions by default. The basic way to enable read and write permissions in Windows XP is via Simple File Sharing. With Windows XP, you can start to implement Simple File Sharing for a folder by right-clicking it in Windows Explorer and choosing Sharing And Security. From the Network sharing and security group on the Sharing tab of the folder Properties dialog box, permit sharing for the folder by clicking the Share This Folder On The Network check box. Windows makes the folder shared by anyone who knows the share name. By default, the share name is the name of the folder, but you can override this assignment by entering a different name in the Share Name box of the Network Sharing And Security group. You can also disable the ability to change files in a shared folder by clearing the Allow Network Users To Change My Files check box. Figure 8-5 shows Windows Explorer along with the Sharing tab of a folder Properties dialog box. The selection on the tab enables Simple File Sharing, but restricts it to reading. These settings are sufficient for using any file in the folder as the source file for the CopyFile method.
| Note |
Because Simple File Sharing provides very unrestricted access to your folders, you should apply it in a highly selective fashion to folders with contents that you deem public. Windows XP has a designated Shared Documents folder. However, by applying Simple File Sharing to a specific folder, you avoid the need to copy or move files from an application folder to a general public folder. |
The CallCopyFileUtility procedure sets the original file and the destination of the copy. The original file is Chapter08.adp in the Access11Files share on the CabSony1 computer. Since the share (Access11Files) contains only one folder, the code designates the share name as the path. The destination of the copied file is the BooksArchive/Access11 folder on the c share of the cab2000 computer. The CopyFileUtility procedure instantiates a FileSystemObject object with the CreateObject function and invokes the CopyFile method for the instance. To run this utility application, you'll first need to update the share and path names for your computing environment. By clearing the Err object and designating that Access recover from errors by processing the next line, the procedure displays one of three messages after completing the attempt to copy the file. If the Err object is still clear, the code announces that the copy attempt succeeded. If the procedure attempts to copy over an existing file without designating that it's OK to do so, the copy attempt fails and the procedure warns the user. Finally, if some unanticipated error occurs, the procedure returns the Err object's Number and Description properties in a message box. Here's the code for the utility.
Sub CallCopyFileUtility() Dim strOrigShare As String Dim strOrigPath As String Dim strOrigFlNm As String Dim strDestShare As String Dim strDestPath As String Dim strDestFlNm As String Dim bolOver As Boolean #Const ForcePathNotFound = False 'Specify inputs for file copy strOrigShare = "\\CabSony1\" strOrigPath = "Access11Files\" strOrigFlNm = "Chapter08.adp" #If ForcePathNotFound = False Then strDestShare = "\\cab2000\c\" #Else strDestShare = "\\xyz\c\" #End If strDestPath = "BooksArchive\Access11\" strDestFlNm = "Chapter08.adp" 'Setting to False can generate a File already 'exist message if the file is there already bolOver = True 'Pass arguments to CopyFileUtility CopyFileUtility strOrigShare, strDestShare, _ strOrigPath, strDestPath, _ strOrigFlNm, strDestFlNm, _ bolOver End Sub Sub CopyFileUtility(strOrigShare As String, _ strDestShare As String, _ strOrigPath As String, _ strDestPath As String, _ strOrigFlNm As String, _ strDestFlNm As String, _ Optional bolOver As Boolean = False) Dim str1 As String Dim str2 As String Dim str3 As String Dim int1 As Integer Const msgTitle = "Programming Microsoft Access 2003" 'Reference FileSystemObject Set fs = CreateObject("Scripting.FileSystemObject") 'Form origin and destination arguments for CopyFile method str1 = strOrigShare & strOrigPath & strOrigFlNm str2 = strDestShare & strDestPath & strDestFlNm 'Clear error and resume from error outcome at the next line 'from the CopyFile method Err.Clear On Error Resume Next fs.CopyFile str1, str2, bolOver 'Print message describing CopyFile outcome Debug.Print Err.Number, Err.Description Select Case Err.Number Case 0 str3 = "Copy succeeded." int1 = vbInformation Case 58 str3 = "File exist at destination already. Choose new name." int1 = vbCritical Case Else str3 = "Unanticipated error." & vbCr & _ "Err.number = " & Err.Number & vbCr & _ "Err.description = " & Err.Description int1 = vbCritical End Select MsgBox str3, int1, msgTitle End Sub
You can test the error paths of the CopyFileUtility procedure by altering the settings for the first procedure in the utility. For example, to generate a message saying that the file already exists, change the assignment for bolOver from True to False and attempt to copy over a file you know exists. One typical unanticipated error occurs when a user attempts to copy to a destination path that does not exist. For example, the user might make a typo when specifying the path. Because it's unlikely that you already have a \\cab2000\c\ computer name and share name in your computing environment, specifying these settings will probably generate a "Path not found" message with an error number of 76. However, even after you update the computer name and share name for your computing environment, users can still generate a "Path not found" message by setting the compiler constant ForcePathNotFound to True (unless they have a computer named xyz with a share named c on your LAN).
The Task Pane introduced with Access 2002 as part of the UI was redesigned for Access 2003. The Task Pane now opens on the right side of the Access desktop window and the Task Pane window is a toolbar. The New File section of this toolbar has three sections. In the first section, users can click on shortcuts to launch various actions, such as creating a blank database (.mdb) file, a blank data access page (discussed in Chapter 14), an Access project based on an existing SQL Server or new SQL Server database, or a new database based on an existing .mdb file. The middle section enables users to search for template files on Microsoft.com. The bottom section is for tapping templates installed with Access 2003. These template files have an .mdz extension. Developers can programmatically add and remove shortcuts on the Task Pane to enable users to add (or prevent them from adding) selected files. The Home view of the Task Pane, which you can open with an icon that looks like a house, includes an Open section. By default, the Task Pane uses this section to list links for recently opened files and a link for exposing the Open dialog box for opening new files. Developers can programmatically add links to the Open section on the Home view of the Task Pane with a syntax similar to that used for adding links to the New section in the New File view.
The following pair of procedures show how to add and remove a Copy Northwind link from the New From Existing File section of the Task Pane. When a user clicks the link, it creates a new file named Northwind1.mdb, which is a copy of the Northwind.mdb file. The new file appears in the same folder as the original one.
Adding a shortcut to the Task Pane requires managing the Task Pane and the proper shortcut specification. Notice that the AddCopyNorthwindLink procedure begins by declaring a NewFile object in the Office library. The declared object requires a reference to the Office 11.0 library in the References dialog box of the VBE window. Next, the procedure sets a reference to a NewFileTaskPane object. The Task Pane has three views—one for searching, another for managing the Windows Clipboard, and a third for managing the creation of new files. The NewFileTaskPane object references the view of the Task Pane for managing the creation of new files. In between two command bar property assignments, the procedure invokes the Add method for the nftpTemp instance. Showing and hiding the Task Pane command bar refreshes the Task Pane to show the outcome of the Add method.
The Add method takes four arguments. Two of these rely on intrinsic constants in enum groups. The action argument specifies what a shortcut does. Its enum group, msoFileNewAction, has three members. These members signify adding, editing, and opening a file. The sample that follows uses the constant for adding a file. The second enum group, msoFileNewSection, denotes the section of the Task Pane. This group contains a separate intrinsic constant for each Task Pane section. This sample specifies the New From Existing File section. The FileName argument designates the source file for the action, and the DisplayName setting determines how the shortcut appears on the Task Pane.
Sub AddCopyNorthwindLink() Dim nftpTemp As Office.NewFile 'Instantiate NewFile object for Task Pane Set nftpTemp = Application.NewFileTaskPane 'Add link to Copy Northwind.mdb database to the New 'From Existing File section of the Task Pane CommandBars("Task Pane").Visible = False nftpTemp.Add _ FileName:="C:\Program Files\Microsoft Office\Office11\" & _ "Samples\Northwind.mdb", _ Section:=msoNewfromExistingFile, _ DisplayName:="Copy Northwind.mdb", _ Action:=msoCreateNewFile CommandBars("Task Pane").Visible = True End Sub Sub RemoveCopyNorthwindLink() Dim nftpTemp As Office.NewFile 'Instantiate NewFile object for Task Pane Set nftpTemp = Application.NewFileTaskPane 'Remove link to Copy Northwind.mdb database from the New 'From Existing File section of the Task Pane CommandBars("Task Pane").Visible = False nftpTemp.Remove _ FileName:="C:\Program Files\Microsoft Office\Office11\" & _ "Samples\Northwind.mdb", _ Section:=msoNewfromExistingFile, _ DisplayName:="Copy Northwind.mdb" CommandBars("Task Pane").Visible = True End Sub
Removing a shortcut from the Task Pane is especially easy. Just take the procedure for adding the shortcut and replace the Add method with the Remove method. You can also drop the Action argument. That's all it takes!
The following listing illustrates the syntax for adding a link to the Open section of the Home view of the Task Pane. The link in the sample opens the Northwind database when a user clicks the link. The procedure for adding the link has the name AddOpenNorthwindLink. Notice the similarity of the syntax to the preceding AddCopyNorthwindLink procedure. Critically, the Section and Action argument values are different for the two procedures. These arguments indicate where the link goes and what the link should do. The Chapter08.mdb contains a procedure named RemoveOpenNorthwindLink that corresponds in syntax to the RemoveCopyNorthwindLink procedure. However, the procedure for removing the Open Northwind.mdb link fails. The remedy to this problem follows the example.
Sub AddOpenNorthwindLink() Dim nftpTemp As Office.NewFile 'Instantiate NewFile object for Task Pane Set nftpTemp = Application.NewFileTaskPane 'Add link to Open Northwind.mdb database to 'the Open section of the Task Pane CommandBars("Task Pane").Visible = False nftpTemp.Add _ FileName:="C:\Program Files\Microsoft Office\Office11\" & _ "Samples\Northwind.mdb", _ Section:=msoOpenDocument, _ DisplayName:="Open Northwind.mdb", _ Action:=msoOpenFile CommandBars("Task Pane").Visible = True End Sub
As mentioned, there is a problem with the Remove method for the Open section of the Task Pane. In this instance, the preceding sample can add a link to the section named Open Northwind.mdb, but a corresponding sample could not remove it. One way to solve this problem is to remove the shortcut via the registry. Start to open the registry by typing regedit into the Open box of the Run dialog box from the Windows Start button. Then, click OK. Next, choose Find from the Edit menu. Search for the shortcut text (Open Northwind.mdb, in this example). With the Windows XP operating system, the editor discovers a reference to the shortcut under HKEY_CURRENT_USER, Software, Microsoft, Office, 11.0, Access, New File. In the test computer I used while writing this book, the shortcut was in the Custom1 folder at the end of the path. Delete the custom folder containing the shortcut specification. After removing the registry entry for the shortcut, you might need to close and open the Task Pane or even your Access session. This refreshes the Task Pane to reflect your changed registry settings.
| Note |
Whenever you make changes to a registry, you first should have a backup copy for restoring it. Detailed commentary on editing, backing up, and restoring registries is beyond the scope of this book. |
|
|