FileSearch Object

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.

Figure 8.2: The FileSearch object is one of the shared Office objects.

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.

Conducting a Basic File 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.

 SubFileSearch1XP() 'SearchintheBooksArchive\Access11pathand 'itssubfoldersonthecdriveofthecab2000 'computerforChapter*.mdb WithApplication.FileSearch 'Startanewsearch .NewSearch 'Setsearchcriteria .LookIn= "\cab2000\c\BooksArchive\Access11" .FileName= "Chapter*.mdb" .SearchSubFolders=True EndWith     WithApplication.FileSearch 'Executethesearch If.Execute()>0Then MsgBox "Therewere " &.FoundFiles.Count&_  " file(s)found." 'Displaynamesofallfoundfiles Fori=1To.FoundFiles.Count MsgBox.FoundFiles(i) Nexti Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound." EndIf EndWith     EndSub

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".

 SubFileSearch1aXP() 'Searchinthetargetpathanditssubfolders 'fordatabasefiles WithApplication.FileSearch     'Startanewsearch .NewSearch 'Setsearchcriteria .LookIn= "\cab2000\c\BooksArchive\Access11" .FileType=msoFileTypeDatabases .SearchSubFolders=True EndWith     WithApplication.FileSearch 'Executethesearch If.Execute()>0Then MsgBox "Therewere " &.FoundFiles.Count&_  " file(s)found." 'Displaynamesofallfoundfiles Fori=1To.FoundFiles.Count MsgBox.FoundFiles(i) Nexti Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound." EndIf EndWith     EndSub

Generalizing a Search and Sorting Its Return Set

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.

 SubCallFileSearch2XP() Dimstr1AsString Dimstr2AsString Dimstr3AsString 'Dimftp1Asfil     str1= "\cab2000\" str2= "c\" str3= "BooksArchive\Access11"     FileSearch2XPstr1,str2,str3     EndSub     SubFileSearch2XP(ComputerNameAsString,_ ShareNameAsString,PathNameAsString,_ OptionalFileType)      DimsngMBAsSingle     'Searchinfolderspecifiedbyargumentsandits 'subfoldersfor*.mdb WithApplication.FileSearch 'Startanewsearch .NewSearch 'Setsearchcriteria .LookIn=ComputerName&ShareName&PathName .FileName= "*.mdb" .SearchSubFolders=True EndWith     WithApplication.FileSearch 'Returnfoundfilesindescendingorderbyfilesize If.Execute(msoSortBySize,msoSortOrderDescending)>0Then Debug.Print "Therewere " &.FoundFiles.Count&_  " file(s)found." Fori=1To.FoundFiles.Count 'ComputefilesizeinMBanddisplaywithfilename sngMB=FileLen(.FoundFiles(i))/(1024^2) Debug.Print.FoundFiles(i)&vbCrLf&vbTab&_  "Filesize(MB): " &Round(CDec(sngMB),3) Nexti Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound." EndIf EndWith     EndSub

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.

Searching Based on File Contents

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.

 SubFileSearch3XP() DimsngStartAsDouble DimsngEndAsDouble Dimint1AsInteger Dimstr1AsString #ConstAllFiles=False     'Searchinafolderonlinkedcomputer 'forfilescontainingADO WithApplication.FileSearch 'Startanewsearch .NewSearch 'Setsearchcriteria .LookIn= "\cab2000\c\BooksArchive\Access11" .SearchSubFolders=False 'Whensearchingfortext,consider 'restrictingthefilesyousearch '*.*canrequiremoretime 'thanmsoFileTypeWordDocuments #IfAllFiles=TrueThen .FileName= "*.*" #Else .FileType=msoFileTypeWordDocuments #EndIf .TextOrProperty= "ADO" EndWith     WithApplication.FileSearch 'Executethesearch sngStart=Now If.Execute()>0Then sngEnd=Now Debug.Print "Thefilesearchtook " &_ DateDiff("s",sngStart,sngEnd)& " seconds." str1= "Therewere " &.FoundFiles.Count&_  " file(s)found.Doyouwanttosee " &_  "them in a series of messages boxes?" IfMsgBox(str1,vbYesNo,_  "ProgrammingMicrosoftAccess2003")=vbYesThen 'Displaynamesofallfoundfiles Forint1=1To.FoundFiles.Count MsgBox.FoundFiles(int1) Nextint1 EndIf Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound." EndIf EndWith     EndSub

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.

Controlling FileType with a Custom Form and Procedure

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.

Figure 8.3: A form for adding members to the FileTypes collection of a FileSearch object. The form also enables executing the search.

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.

Note

The form's combo box shows the names of all the intrinsic constants for FileType designations but stores a constant's number when a user makes a selection. The combo box uses the same lookup table as the DLookup function that decodes an intrinsic constant value to the name for the constant. However, the combo box works the table in reverse from the DLookup function in the final loop of the Form_Open event procedure.

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.

 Publicfls1AsFileSearch Publicint1AsInteger Publicstr1AsString     PrivateSubForm_Open(CancelAsInteger)     'SetRowSourceTypetoValueListsoyoucanuse 'AddItemandRemoveItemmethods Me.lstExistingFileTypes.RowSourceType= "ValueList"     'Setformforunbounddatause Me.RecordSelectors=False Me.NavigationButtons=False Me.DividingLines=False     'Removeallexistingitemsfromthelistbox 'showingselectedfiletypes Forint1=Me.lstExistingFileTypes.ListCount-1To0Step-1 Me.lstExistingFileTypes.RemoveItemint1 Nextint1     'SetFileSearchvariable Setfls1=Application.FileSearch     'LoopthroughFileTypesinFileSearchobjectand 'addthemtothelistbox 'FileTypescollectionis1-basedandlistboxitems 'are0-based Forint1=1Tofls1.FileTypes.Count str1= "[msoFileTypeNumber]= " &_ CStr(fls1.FileTypes.Item(int1)) Me.lstExistingFileTypes.AddItem_ Item:=DLookup("[msoFileTypeName]",_  "msoFileTypeEnum",str1),_ Index:=Me.lstExistingFileTypes.ListCount Nextint1     EndSub      PrivateSubcmdDesignateSingle_Click() OnErrorGoToNullSingleTrap 'RefreshtheFileTypescollectionoftheFileSearch 'objectwiththecomboboxselection fls1.FileType=Combo0     'Removeallexistingitemsfromthelistbox 'showingselectedfiletypes Forint1=Me.lstExistingFileTypes.ListCount-1To0Step-1 Me.lstExistingFileTypes.RemoveItemint1 Nextint1     'Refreshthelistboxwiththenewlyselecteditem RefreshWithNewFileTypeinFileSearch     'Blankcomboafterprocessingselection Combo0= ""     NormalExit: ExitSub     NullSingleTrap: IfErr.Number=94Then MsgBox "Selectafiletypefromthecombo " &_  "boxbeforeclickingthebutton.",vbCritical,_  "ProgrammingMicrosoftAccess2003" Else MsgBox "CallSupporttoreportthisproblem.",_ vbCritical, "ProgrammingMicrosoftAccess2003" EndIf     EndSub     PrivateSubcmdAddTo_Click() OnErrorGoToNullAddTrap 'Addthecomboboxselectiontotheexistinglist 'ofmembersintheFileTypescollectionofthe 'FileSearchobject fls1.FileTypes.AddCombo0     'Refreshthelistboxwiththenewlyselecteditem RefreshWithNewFileTypeinFileSearch     'Blankcomboafterprocessingselection Combo0= ""     NormalExit: ExitSub     NullAddTrap: IfErr.Number=94Then MsgBox "Selectafiletypefromthecombo " &_  "boxbeforeclickingthebutton.",vbCritical,_  "ProgrammingMicrosoftAccess2003" Else MsgBox "CallSupporttoreportthisproblem.",_ vbCritical, "ProgrammingMicrosoftAccess2003" EndIf     EndSub     SubRefreshWithNewFileTypeinFileSearch() 'Dimint1AsInteger 'Dimstr1AsString     'FindtheindexforthelastitemintheFilesTypescollection int1=fls1.FileTypes.Count str1= "[msoFileTypeNumber]= " &CStr(fls1.FileTypes.Item(int1))     'AddnamecorrespondingtotheFileTypenumbertothebottom 'ofthe1istbox Me.lstExistingFileTypes.AddItem_ Item:=DLookup("[msoFileTypeName]", "msoFileTypeEnum",str1),_ Index:=Me.lstExistingFileTypes.ListCount     EndSub     PrivateSubcmdSearch_Click()     'Launchsearchwithsettingfromform 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.

 SubFileSearch1a3aXP() 'SearchinProgramFilesfolderanditssubfolders WithApplication.FileSearch 'Startanewsearch. 'Setsearchcriteria. .LookIn= "C:\PMASamples" .SearchSubFolders=True EndWith     WithApplication.FileSearch 'Executethesearch If.Execute()>0Then str1= "Therewere " &.FoundFiles.Count&_  " file(s)found." MsgBoxstr1,vbInformation,_  "ProgrammingMicrosoftAccess2003" Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound.",vbInformation,_  "ProgrammingMicrosoftAccess2003" EndIf EndWith     EndSub

Specifying Multiple Search Criteria

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.

Figure 8.4: You use the members of the MsoCondition enumeration group to specify conditions for advanced criteria in the PropertyTests collection of the FileSearch object.

 SubFileSearch4XP() Dimfls1AsFileSearch Dimstr1AsString Dimint1AsInteger     Setfls1=Application.FileSearch     'SetLookInandSearchSubFolderproperties Withfls1 .NewSearch .LookIn= "\cab2000\c\ProgramFiles\" &_  "MicrosoftOffice\Office10\Samples" .SearchSubFolders=False EndWith     'RemovedefaultPropertyTestforOfficefiles fls1.PropertyTests.Remove(1)     'SetapairofPropertyTeststofindallWebpages 'lastmodifiedoneither10/30/2000or10/31/2000 Withfls1.PropertyTests .AddName:="FilesofType",_ Condition:=msoConditionFileTypeWebPages,_ Connector:=msoConnectorOr .AddName:="LastModified",_ Condition:=msoConditionAnytimeBetween,_ Value:="11/1/2002",SecondValue:="12/31/2003" EndWith     'DisplayPropertyTests Forint1=1Tofls1.PropertyTests.Count WithApplication.FileSearch.PropertyTests(int1) str1= "Thisisthesearchcriteria: " &vbCrLf&_  "Thenameis: " &.Name& ". " &vbCrLf&_  "Theconditionis: " &.Condition If.Value<> "" Then str1=str1& "." &vbCrLf& "Thevalueis: " &.Value If.SecondValue<> "" Then str1=str1_ & ". " &vbCrLf& "Thesecondvalueis: " _ &.SecondValue& ",andtheconnectoris " _ &.Connector EndIf EndIf MsgBoxstr1     EndWith Nextint1     'Displayreturnsetfrompropertytests Withfls1 'Executethesearch If.Execute()>0Then MsgBox "Therewere " &.FoundFiles.Count&_  " file(s)found." 'Displaynamesofallfoundfiles Forint1=1To.FoundFiles.Count MsgBox.FoundFiles(int1) Nextint1 Else 'Ifnofilesfound,sayso MsgBox "Therewerenofilesfound." EndIf EndWith     EndSub

A Utility for Copying Files Based on FileSystemObject

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.

Figure 8.5: The Access11Files Properties dialog box with the settings to implement read-only Simple File Sharing with Windows XP.

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.

 SubCallCopyFileUtility()  DimstrOrigShareAsString DimstrOrigPathAsString DimstrOrigFlNmAsString DimstrDestShareAsString DimstrDestPathAsString DimstrDestFlNmAsString DimbolOverAsBoolean #ConstForcePathNotFound=False     'Specifyinputsforfilecopy strOrigShare= "\CabSony1\" strOrigPath= "Access11Files\" strOrigFlNm= "Chapter08.adp" #IfForcePathNotFound=FalseThen strDestShare= "\cab2000\c\" #Else strDestShare= "\xyz\c\" #EndIf strDestPath= "BooksArchive\Access11\" strDestFlNm= "Chapter08.adp"     'SettingtoFalsecangenerateaFilealready 'existmessageifthefileistherealready bolOver=True     'PassargumentstoCopyFileUtility CopyFileUtilitystrOrigShare,strDestShare,_ strOrigPath,strDestPath,_ strOrigFlNm,strDestFlNm,_ bolOver     EndSub   SubCopyFileUtility(strOrigShareAsString,_ strDestShareAsString,_ strOrigPathAsString,_ strDestPathAsString,_ strOrigFlNmAsString,_ strDestFlNmAsString,_ OptionalbolOverAsBoolean=False) Dimstr1AsString Dimstr2AsString Dimstr3AsString Dimint1AsInteger ConstmsgTitle= "ProgrammingMicrosoftAccess2003"     'ReferenceFileSystemObject Setfs=CreateObject("Scripting.FileSystemObject")     'FormoriginanddestinationargumentsforCopyFilemethod str1=strOrigShare&strOrigPath&strOrigFlNm str2=strDestShare&strDestPath&strDestFlNm     'Clearerrorandresumefromerroroutcomeatthenextline 'fromtheCopyFilemethod Err.Clear OnErrorResumeNext fs.CopyFilestr1,str2,bolOver     'PrintmessagedescribingCopyFileoutcome Debug.PrintErr.Number,Err.Description SelectCaseErr.Number Case0 str3= "Copysucceeded." int1=vbInformation Case58 str3= "Fileexistatdestinationalready.Choosenewname." int1=vbCritical CaseElse str3= "Unanticipatederror." &vbCr&_  "Err.number= " &Err.Number&vbCr&_  "Err.description= " &Err.Description int1=vbCritical EndSelect MsgBoxstr3,int1,msgTitle     EndSub

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).

Empowering Users to Create and Open Files from the Task Pane

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.

Note	End users can open the Task Pane toolbar by choosing New from the File menu. Developers can program the Task Pane by using the NewFileTaskPane property, which returns a NewFile object that represents an item in the New section of the New File view of the Task Pane.

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.

 SubAddCopyNorthwindLink()  DimnftpTempAsOffice.NewFile     'InstantiateNewFileobjectforTaskPane SetnftpTemp=Application.NewFileTaskPane     'AddlinktoCopyNorthwind.mdbdatabasetotheNew 'FromExistingFilesectionoftheTaskPane CommandBars("TaskPane").Visible=False nftpTemp.Add_ FileName:="C:\ProgramFiles\MicrosoftOffice\Office11\" &_  "Samples\Northwind.mdb",_ Section:=msoNewfromExistingFile,_ DisplayName:="CopyNorthwind.mdb",_ Action:=msoCreateNewFile CommandBars("TaskPane").Visible=True     EndSub     SubRemoveCopyNorthwindLink()  DimnftpTempAsOffice.NewFile     'InstantiateNewFileobjectforTaskPane SetnftpTemp=Application.NewFileTaskPane     'RemovelinktoCopyNorthwind.mdbdatabasefromtheNew 'FromExistingFilesectionoftheTaskPane CommandBars("TaskPane").Visible=False nftpTemp.Remove_ FileName:="C:\ProgramFiles\MicrosoftOffice\Office11\" &_  "Samples\Northwind.mdb",_ Section:=msoNewfromExistingFile,_ DisplayName:="CopyNorthwind.mdb" CommandBars("TaskPane").Visible=True     EndSub

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.

 SubAddOpenNorthwindLink()  DimnftpTempAsOffice.NewFile     'InstantiateNewFileobjectforTaskPane SetnftpTemp=Application.NewFileTaskPane     'AddlinktoOpenNorthwind.mdbdatabaseto 'theOpensectionoftheTaskPane CommandBars("TaskPane").Visible=False nftpTemp.Add_ FileName:="C:\ProgramFiles\MicrosoftOffice\Office11\" &_  "Samples\Northwind.mdb",_ Section:=msoOpenDocument,_ DisplayName:="OpenNorthwind.mdb",_ Action:=msoOpenFile CommandBars("TaskPane").Visible=True     EndSub

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.