To assist users with file processing, the Microsoft Windows operating system provides the Save As dialog box. This dialog box has been designed or modified many times so much that its appearance depends on the operating system. Here is the Save As dialog box on Microsoft Windows Vista:

The Save As dialog box allows a user to save a new file, to change the name of an existing file, to create a new folder, to rename a folder, to change the path to a file, or to change the extension of a file.

In a VBA application, you can give your users the ability to save the values on a form (or a workbook) by displaying the Save As dialog box. To assist you with this, the Application class is equipped with a property named FileDialog.

The FileDialog property of the Application is presented as follows:

Public Application.FileDialog(ByVal DialogType As MsoFileDialogType) As FileDialog

This property takes as argument the type of dialog box you want to display. The types are members of the MsoFileDialogType enumeration. If you want to display a Save As dialog box, specify the argument as msoFileDialogSaveAs.

As you can see from the syntax, the FileDialog property of the Application class is of type FileDialog. Therefore, after initializing it, assign it to a FileDialog variable you would have previously declared. Remember that you assign an object using the Set operator. Here is an example:

Sub ShowSaveAs()
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
End Sub

After initializing the FileDialog variable, you can present the dialog box to the user.

To support the ability to present the Save As dialog box to the user, the FileDialog class is equipped with the Show() method. Its syntax is:

Public Function Show As Boolean

This method takes no argument. Here is an example of calling it:

Sub ShowSaveAs()
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

    dlgSaveAs.Show
End Sub

When the Save As dialog box is presented to the user, he or she can take action. The most regular action, as far as the dialog box is concerned, consists of specifying the name of the file and clicking Save. Still, the user can change his/her mind by clicking Cancel.

The ability to take action on the dialog box is represented by a Boolean (or Integer) value that the Application.FileDialog.Show() method returns. You can get the Boolean value of the action that the user performed by storing it in a variable. Here is an example:

Sub ShowSaveAs()
    Dim Action As Boolean
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    Action = dlgSaveAs.Show
    MsgBox CStr(Action)

End Sub

You can also get the returned value of the Application.FileDialog.Show() method as an Integer:

Sub ShowSaveAs()
    Dim Action As Integer
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    Action = dlgSaveAs.Show
End Sub

The returned value of the Application.FileDialog.Show() method is as follows:

• If the user enters a file name and clicks Save, the Application.FileDialog.Show() method returns True or -1
• If the user clicks Cancel, the Application.FileDialog.Show() method returns False or 0

After getting this value, it is your turn to take action. For example, if the user had entered a file name and clicked Save, you would then have to save the file, which the Save As dialog box purposely does not do for you (because you are free to actually save the file or not, or to check some details before allowing that the file be saved).

You may have noticed that the Save As dialog box presents many options to the user. Some of these options you can change easily. Some others we will review later. And some other are deep into the operating system so much that you may need some gymnastic (a lot of code) to change them.

The Title of the Save As Dialog Box

By default, when the Save As dialog box appears to the user, its title bar displays "Save As". If you want, you can display a title of your choice. To support this, the FileDialog class is equipped with a property named Title.

To specify a title of your choice, assign a string to the Title property. Here is an example:

Sub ShowSaveAs()
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    dlgSaveAs.Title = "Save Current File"
    
End Sub

Normally, most users are trained to know what to do with the Save As dialog box. For example, when it is presented, the user can type a name for a file in the File Name combo box and click Cancel. This would save the file in the default or indicated folder. The combination of the name of the file, its folder (and sub-folder(s)), and its drive (hard drive or network drive) is called its path.

To give you access to the file name and path, the FileDialog class is equipped with a property named SelectedItems, which is a collection:

Public Application.FileDialog.SelectedItems(ByVal Item As Long) As Collection

Each of the items in the Application.FileDialog.SelectedItems collection is of type Variant:

Public Item As Variant

If the user is saving one file, pass the index to this collection as 1. Here is an example:

Sub ShowSaveAs()
    Dim Action As Boolean
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    Rem Action = dlgSaveAs.Show
    Rem MsgBox CStr(Action)

    If dlgSaveAs.Show = True Then
        MsgBox dlgSaveAs.SelectedItems(1)
    End If
End Sub

The returned value of a call to Application.FileDialog.SelectedItems(Value) is the complete path to the file that is being saved, not just the name of the file. Still, the path is sometimes represented along with the name of the file, as the file name. Consequently, you can get the returned value of the Application.FileDialog.SelectedItems(Value) and store it in a variable. Here is an example:

Sub ShowSaveAs()
    Dim Filename As String
    Dim Action As Integer
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    Filename = dlgSaveAs.SelectedItems(1)
    
    If dlgSaveAs.Show = True Then
        MsgBox Filename
    End If
End Sub

Unfortunately (or fortunately, depending on how you analyze the situation), and despite its suggestive name, the Save As dialog box does not save a file. Its job is to present a convenient dialog box to the user who is usually familiar with it. It is up to you, as the application programmer, to save the file, or to indicate to the operating system how to save the file; that is, to control what should be saved and what should be ignored.

By default, when the Save As dialog box is presented to the user, if the file was not previously save, the File Name combo box would be empty, expecting the user to enter a name for the file. Of course, to save the file, the user would have to enter a name. Fortunately, you can programmatically assist the user by suggesting a file name.

To assist you with providing a default file name to the Save As dialog box, the FileDialog class is equipped with a property named InitialFileName. To use it, before presenting the dialog box, assign a string to this property. Here is an example:

Sub ShowSaveAs()
    Dim Filename As String
    Dim Action As Integer
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

    dlgSaveAs.InitialFileName = "Example.xlsx"

    dlgSaveAs.Show
End Sub

If you provide only the name of the file, the dialog box would select the content of the default user folder, which in Microsoft Windows <= XP is My Documents and in Microsoft Windows Vista is Documents. If you want, you can provide a complete path, including the drive, the folder, the sub-folder(s) if any, up to the name of the file. You can also provide a complete path to a network drive. Here is an example:

Sub ShowSaveAs()
    Dim Filename As String
    Dim Action As Integer
    Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

    dlgSaveAs.InitialFileName = "\\support\Common\Example.xlsx"

    dlgSaveAs.Show
End Sub

If you do this, the Save As dialog box would make an attempt to locate the path. If the path is valid, it would be selected in the dialog box and the file would appear. If the path is not valid, you would receive an error.

After providing the necessary information on the Save As dialog box, the user can press Enter or click the appropriate button to finalize the action. By default, the button to click is marked Save. The label (string) on the button is already to as its caption. If you want, instead of Save, you can make it display any caption of your choice.

To let you specify a caption of your choice, the FileDialog class is equipped with a property named ButtonName. Therefore, to set a caption, assign the desired string to this property. Here is an example:

Private Sub ShowSaveAs()
    Dim dlgSaveAs As FileDialog

    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

    dlgSaveAs.ButtonName = "Store Current File"
    
    dlgSaveAs.Show
End Sub

This would produce:

Once a file exists on a medium, the user can open it to view its document. Most of the time, this is done using a dialog box. The Microsoft Windows family of operating systems provide a convenient dialog box for this purpose. As mentioned for the Save As dialog box, the appearance (or look) of the Open dialog box depends on the (version of the) operating system. Here is the Open dialog box of Microsoft Windows Vista:

The Open dialog box allows the user to open an existing file or to create a folder (there are many other operations a user can perform on this dialog box.

Like the Save As dialog box, the Open File dialog box is available through the FileDialog class. This class is represented in the Application class by the FileDialog property. As mentioned already, The FileDialog property of the Application class is presented as follows:

Public Application.FileDialog(ByVal DialogType As MsoFileDialogType) As FileDialog

To create an Open File dialog box, specify the argument as msoFileDialogOpen.

As seen for the Save As dialog box, the FileDialog property is a FileDialog object. This means that, when accessing this property for the first time, assign its call to a FileDialog variable. Here is an example:

Sub OpenFile()
    Dim dlgOpenFile As FileDialog

    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
End Sub

After initializing the FileDialog variable, to present the dialog box to the user, call the  Show() method of the FileDialog class. Here is an example:

Sub OpenFile()
    Dim dlgOpenFile As FileDialog

    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)

    dlgOpenFile.Show
End Sub

As seen previously, the Application.FileDialog.Show() method returns a Boolean or Integer value:

• If the user enters or select a file name and clicks Open, 
• If the user clicks Cancel, the Application.FileDialog.Show() method returns False or 0

If the Application.FileDialog.Show() method returns True or -1, which indicates that the user had selected a file, you can open it. The Open File dialog box does not open a file for you. You have to do it yourself.

The Open File dialog shares many characteristics with the Save As dialog box. These common characteristics are defined in the FileDialog class.

As mentioned for the Save As dialog box, the Open File dialog box displays a default title, which is File Open. If you want to display a custom title, assign the desired string to the Title property of the FileDialog class. Here is an example:

Sub OpenFile()
    Dim dlgOpenFile As FileDialog

    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)

    dlgOpenFile.Title = "Open Existing Workbook"
    
    dlgOpenFile.Show
End Sub

This would produce:

When an Open File dialog box comes up, a user usually knows that he or she is supposed to select a file and open it. As an option, you can provide file that would be opened. To make this possible, we saw that the FileDialog class is equipped with the InitialFileName property. Once again, you can provide either only the name of a file or rather its complete path.

Opening One or More Files

The Open File allows a user to open one or more files. The ability to open only one or many files is controlled by the AllowMultiSelect Boolean property of the FileDialog class. By default this property is set to True. As such:

• To select many files in a range in the Open File dialog box, the user can click one file, press Shift, and click the file on the other side of the range
• To select many files in different positions in the list, the user can click one file, press and hold Ctrl, then click each file

After making the selection(s), the user can click Open. The files that have been selected are stored in the SelectedItems of the FileDialog class. As mentioned previously, this property is presented as follows:

Public Application.FileDialog.SelectedItems(ByVal Item As Long) As Collection

Remember that each member of the Application.FileDialog.SelectedItems collection is Variant type. The first file the user selects holds an index of 1. Here is an example:

Sub OpenFile()
    Dim dlgOpenFile As FileDialog

    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)

    If dlgOpenFile.Show = True Then
        MsgBox dlgOpenFile.SelectedItems(1)
    End If
End Sub

If the user selects more than one file, the second file has an index of 2, and so on.

Besides calling the Show() method of the FileDialog class, the Application class provides its own means of opening a file. To support it, the Application class provides the FindFile() method. Its syntax is:

Public Function Application.FindFile() As Boolean

If you call this method, the Open File dialog with its default settings would come up. The user can then select a file and click open. If the file is a workbook, it would be opened and its content displayed in Microsoft Excel. If the file is text-based, or XML, etc, Microsoft Excel would proceed to open or convert it.

By default, when the Save As dialog box is presented to the user, the user can just enter the name of the file and click Save. The dialog box would internally set the file extension to .xlsx. This is the default file extension in Microsoft Office Excel 2007. If you are creating a VBA application, you probably plan to save value that are not necessarily meant for a spreadsheet. You can create files and use any extension of your choice.

Most users are trained to change the extension of a file in the Save As dialog box. One way you can accommodate users or even control the types of files that your application allows is to create a filter. A filter is a list of file types and extension that your application supports.

To supports filters, the FileDialog class is equipped with a property named Filters. The Filters property uses a type of internal list but provided as one string. Inside the string, you create sections that each represents a filter. Each section uses this formula:

Prompt|Extension

The Prompt is a sub-section that defines what the user would see in the Save As Type combo box. An example would be Microsoft Excel 2007 Workbook. Such a string does not let the user know what actual extension the file would use. Therefore, as a courtesy, you can specify, between parentheses, the Extension that would be applied if this extension is used. The Prompt can be Microsoft Excel 2007 Workbook (*.xlsx). In this case, the extension used would be xlsx. The asterisk * lets the user know that whatever is provided as the file name would be used in place of the asterisk. The period indicates the separation from the file to its extension. This means that the characters on the left of the period would be the file name, the characters on the right side of the period would be used as the actual file extension.

To specify the extension that the operating system would use to associate to the file, you provide a second part of the string as Extension. An example of a string as an extension is:

Microsoft Excel 2007 Workbook (*.xlsx)|*.xlsx

If you want to provide various extensions to your Save As dialog box, you can separate them with a | symbol. An example would be:

HTML Files (*.htm)|*.htm|Active Server Pages (*.asp)|*.asp|Perl Script (*.pl)|*.pl

To specify the filters to your Save As dialog box, assign it to the Filters property to the FileDialog variable. Here is an example:

Public Sub InitializeComponent()
            FileSaver = New SaveFileDialog
            FileSaver.Filter = "HTML Files (*.htm)|*.htm|" & _
                                  "Active Server Pages (*.asp)|*.asp|" & _
                                  "Apache Files (*.php)|*.php|" & _
                                  "Perl Script (*.pl)|*.pl|" & _
                                  "All Files|"

End Sub

This would produce:

A filter organizes its extensions and categories as indexes. The above filter has the following indexes:

Index 1 = HTML Files (*.htm)
Index 2 = Active Server Pages (*.asp)
Index 3 = Apache Files (*.php)
Index 4 = Perl Script (*.pl)
Index 5 = All Files;

After creating a filter, when the dialog box comes up, the Save As Type combo box displays the first index of the filter. If you want, instead of displaying the first index by default, you can specify another index. To specify the desired index at design time, change the value of the FilterIndex field in the Properties window. To programmatically specify it, assign a value to the FileSaveDialog.FilterIndex property. Here is an example:

Public Sub InitializeComponent()

            FileSaver = New SaveFileDialog
            FileSaver.Filter = "HTML Files (*.htm)|*.htm|" & _
                                  "Active Server Pages (*.asp)|*.asp|" & _
                                  "Apache Files (*.php)|*.php|" & _
                                  "Perl Script (*.pl)|*.pl|" & _
                                  "All Files|"

            FileSaver.FilterIndex = 3

End Sub

Once you know the types of files that your application will be dealing with, you can make your dialog box friendly by displaying the most likely extension for a document created using your application. For example, if you create a text-based application, users are more likely to create a text file with it. If you create a rich text-based application, users are more likely to create a Rich Text Format file with it. This most likely extension is known as the default extension, it allows the user not to provide an extension in the most likely cases when saving a file. By simply providing a file name and clicking Save, the operating system would associate the file with the default extension. Of course, if you create a filter, the user can specify a desired allowed extension.