Get OpenFilename and Get SaveAsFilename Methods

For many years now, Excel has offered its programmers two handy VBA methods for displaying the File Save As and File Open dialog boxes: GetOpenFilename and GetSaveAsFilename. These methods are available only in Excel and can still be used in Excel 2002 if backward compatibility is required.

The GetOpenFilename method displays the Open dialog box, where you can select the name of a file to open. The second method (GetSaveAsFilename) shows the Save As dialog box.

1. Try out the following instructions in the Immediate window:

Application.GetOpenFilename Application.GetSaveAsFilename Application.GetSaveAsFilename ("Plan2.xls")

The GetOpenFilename method gets a filename from the user without actually opening the specified file. This method has four optional arguments. The most often used are the first and third arguments, as shown in the following table:

GetOpenFilename Description Arguments fileFilter This argument determines what appears in the dialog box's

Save as type field. For example, to display the text "Excel Files(*.xls)" in the Files of type drop-down list, you should enter the following text as fileFilter: "(Excel Files(*.xls), *.xls"). The first part of the filter (before the comma) determines the text to be displayed in the Files of type drop-down list. The second part (after the comma) specifies which files are to be displayed. Be sure to try out the example following the table.

title This is the title of the dialog box. If omitted, the dialog box will appear with the default title "Open."

To see how these arguments are used, enter the following statement in the Immediate window (be sure to enter it on one line):

Application.GetOpenFilename("Excel Files(*.xls), *.xls"),,"Highlight _ the File"

The GetOpenFilename method returns the name of the selected or specified file. This name can be used later by your VBA procedure to open the file. For example:

yourFile = Application.GetOpenFilename

?yourFile

C:\EXCEL\Mark.xls

Workbooks.Open Filename:=yourFile

In the example above, the filename is assigned to the variable yourFile. The two entries that follow inquire about the filename (?yourFile) and display its name (C:\EXCEL\Mark.xls). The fourth line opens the file as specified by the contents of the variable yourFile. The GetOpenFilename method returns false if you cancel the dialog box by pressing Esc or clicking Cancel.

The GetSaveAsFilename method returns a filename and path; however, it does not automatically save the specified file. To suggest the name of the file, enter the following instruction:

Application.GetSaveAsFilename ("Plan2.xls")

If you omit the filename, Excel will display the name of the active file. When using the GetSaveAsFilename method, you can specify the file filter as well as the dialog box's custom title:

yourFile = Application.GetSaveAsFilename("Plan2.xls", _ "Excel Files(*.xls), *.xls",,"Name your file")

To display the Save As dialog box, assign the result of the GetSaveAs-Filename method to a variable, as shown above.

0 0

Post a comment