File Related Members

Let us take a brief look at the members that are related to file operations. DefaultFilePath property (R/W String)

This property returns or sets the default path that Microsoft Excel uses when it opens or saves files. This setting can also be changed by the user in the General tab of the Options dialog. DefaultSaveFormat property (R/W Long)

This property returns or sets the default format for saving files. The default for this property is xlWorkbookNormal, indicating the normal workbook format for the current version of Excel. The possible values for this property are the XLFileFormat constants shown in Table 16-4.

Table 16-4. XLFileFormat constants

xlAddln (18)

xlExcel5 (39)

xlWJ3 (40)

xlCSV (6)

xlExcel7 (39)

xlWJ3FJ3 (41)

xlCSVMac (22)

xlExcel9795 (43)

xlWK1 (5)


xlHtml (44)<v9>

xlWK1ALL (31)

xlCSVWindows (23)

xllntlAddIn (26)

xlWK1FMT (30)

xlCurrentPlatformText (-4158)

xllntlMacro (25)

xlWK3 (15)

xlDBF2 (7)

xlSYLK (2)

xlWK3FM3 (32)

xlDBF3 (8)

xlTemplate (17)

xlWK4 (38)

xlDBF4 (11)

xlTextMac (19)

xlWKS (4)

xlDIF (9)

xlTextMSDOS (21)

xlWorkbookNormal (-4143)

xlExcel2 (16)

xlTextPrinter (36)

xlWorks2FarEast (28)

xlExcel2FarEast (27)

xlTextWindows (20)

xlWQ1 (34)

xlExcel3 (29)

xlUnicodeText (42)<v9>

xlXMLSpreadsheet (46)<v10>

xlExcel4 (33)

xlWebArchive (45)<v10>

xlExcel4Workbook (35)

xlWJ2WD1 (14) FileDialog property

This property programmatically opens a file-related dialog box and returns a FileDialog object. It takes as parameter one of the following constants to indicate the type of dialog:

msoFileDialogFilePicker msoFileDialogFolderPicker msoFileDialogOpen msoFileDialogSaveAs

To illustrate, the following code:

' Open dialog

With Application.FileDialog(msoFileDialogFolderPicker) .Show

' Display path MsgBox .SelectedItems(1) End With allows the user to select a folder and then displays the folder's complete path. FindFile method

This method, whose syntax is:

Application.FindFile displays the Open dialog box. If a file is opened successfully by the user, the method returns True. If the user cancels the dialog box, the method returns False. GetOpenFilename method

This method displays the Open dialog box and gets a filename or filenames from the user but does not open the files. Its syntax is:


Filterlndex, Title, ButtonText, MultiSelect)

The optional FileFilter parameter is a string that specifies what to put in the "Files of type" drop-down list box in the Open dialog. In other words, it specifies file filtering criteria. This string is in two parts, of the form:

description, filefilter

The first part is the description of the file type, and the second part is the MS-DOS wildcard filefilter specification. The two parts are separated by a comma. Note that the first part is the string that appears in the "Files of type" drop-down box in the Open dialog box. Thus, the first part also includes the wildcard file-filter specification. Perhaps a few examples will help clarify:

Text files

It is also possible to use multiple wildcard file filters, as in:

(Note the semicolons.) If the FileFilter argument is omitted, the default is:

Note that FileFilter can consist of more than one filter specification, separated by commas, as in:

Debug.Print Application.GetOpenFilename( "Text Files (*.txt),*.txt,

In this case, the optional FilterIndex parameter specifies which of the filters appears in the "Files of type" drop-down list box. For instance, the following will cause the second filter (backup files) to appear in the "Files of type" drop-down list box:

Debug.Print Application.GetOpenFilename( "Text Files (*.txt),*.txt,

Backup Files (*.xlk; *.bak), *.xlk; *.bak", 2)

The optional Title parameter specifies the title of the dialog box. If this argument is omitted, the title is Open. The ButtonText parameter is ignored by Windows, but used on the Macintosh.

The optional MultiSelect property is set to True to allow multiple filenames to be selected and False to allow only one filename to be selected. The default value is False. To select multiple files from the Open dialog, the user must hold down the Ctrl or Shift key.

The method returns the selected filename or the name entered by the user. The returned name may also include a path specification. If the MultiSelect parameter is True, the return value is an array of the selected filenames (even if only one filename is selected). The method returns False if the user cancels the dialog box.

When Multiselect is True, we can determine the number of files selected by the user by using the UBound function to get the upper bound for the returned array, as in:

NumFiles = UBound(Application.GetOpenFilename(MultiSelect:=True))

Note finally that this method may change the current drive or folder. GetSaveAsFilename method

This method is similar to the GetOpenFilename method, but instead displays the Save As dialog box and gets a filename from the user without saving any files. The syntax is:

Application.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

The optional InitialFilename parameter specifies the filename that is placed in the "File name" text box on the Save As dialog. If this argument is omitted, Excel uses the name of the active workbook. The other parameters (and return values) are the same as for the GetOpenFilename method. As with GetOpenFilename, this method may change the current drive or folder. RecentFiles property (Read-Only)

This property returns a RecentFiles collection that represents the list of recently used files. There are two interesting aspects to the RecentFiles collection. First, it has a Maximum property that returns or can be set to the maximum number of files allowed in the recently used files list that appears on Excel's File menu. This number must be an integer between 0 and 9, inclusive. Thus, the code:

MsgBox Application.RecentFiles.Maximum displays the current value.

Second, we can print a list of the filenames of the most recently used files as follows (of course, you may want to do more than print this list):

Dim rf As RecentFile

For Each rf In Application.RecentFiles

Debug.Print rf.Name Next

Note that the RecentFiles collection contains RecentFile objects, and not simply the names of the recently used files, as one might expect. SaveWorkspace method

This method saves the current workspace. Its syntax is:

Application.SaveWorkspace(Filename) where Filename is an optional filename for the xlw file.

0 0

Post a comment