The FileDialog object in Excel allows one to show users precreated but customizable windows to users so they can open, save, and pick files and folders. In this tutorial, the last of our FileDialog series, we will take a look at how to save files under different names while retaining the original file (Save As). If you haven’t read our other FileDialog tutorials, no worries. This tutorial is entirely self-contained. Of course I encourage you to check out the others to round out your knowledge of VBA!
If you need a fully customizable “Save As” experience, you’ll need to build your own Userforms from scratch. FileDialogs offer a good compromise, wherein you can customize a bit but the form layout is already created by Microsoft.
We will step through setting up and modifying the object before getting into the methods (.Show and .Execute). There is a fully functional macro at the end if you want to jump there and begin playing with the code. That’s also where you’ll find a screenshot of our customized VBA Save As dialog box.
The String-based Properties
If you’re familiar with our FileDialog series, you’ll recall the FileDialog object has four settings. We’ll use the msoFileDialogSaveAs setting in this tutorial to teach you how to display the Save As dialog box. The first step to displaying the Save As dialog is to set up your variables, which you can do with this code:
Dim oFD As FileDialog
Set oFD = Application.FileDialog(msoFileDialogSaveAs)Make powerful macros with our free VBA Developer Kit It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.
The msoFileDialogSaveAs box has the same properties and methods as the other FileDialogs, but only a subset of them actually do anything with the Save As dialog. This is particularly important for the Filters collection, which we will discuss below.
The Title and Button Properties
Two simplest properties to modify are Title and ButtonName. These properties both accept a string and control the text on your Save As dialog. The title refers to the caption at the top of the window, and the button refers to the execute/go button. The default settings are “File Save” and “Save” respectively. You can set these directly using VBA, since they are read/write properties:
oFD.Title = "Choose a Location and Name of the File to Save This File"
oFD.ButtonName = "Click to Save"If you want a keyboard shortcut for your users, you can prepend an ampersand to the letter in the ButtonName variable, which will underline the letter and tell the OS to treat the key press as a shortcut. For example, if you wanted a shortcut for “a”, you could write
oFD.ButtonName = "Click to S&ave"and users can press Alt+a to save. Just make sure the key you choose isn’t already in use as a shortcut for another property by default.
The Initial Name (and Location) Property
As you might expect, the InitialFileName property sets the string that appears in the file name box in the dialog. However, this property controls more than just the file name. Not only does the InitialFileName property set the name, it also sets the default location.
To set a default file name, you add a line of code like this:
oFD.InitialFileName = "myFile"When you do that, you’ll get a dialog that opens to some default location (likely the Documents folder) with a preset filename of InitialFileName property to a file path, your Save As dialog box will automatically open to that location.
Consider these two statements:
oFD.InitialFileName = "C:\Users\"
oFD.InitialFileName = "C:\Users\myFile"The first statement will open the dialog in the Users folder, while the second will open the dialog in the Users folder and preset the filename to
Filters, File Types, and Views
When we want to open a file, we can change filters around and do all kinds of interesting things with the Filters collection. These filters don’t really make as much sense when you want to save a file. With the VBA Save As dialog box, we can only set the default filter via the FilterIndex property. There are 25+ filters that populate the FileDialog upon its instantiation, and you can choose one of them. However, you can’t clear the list and you can’t add your own filters - mainly because Excel can’t save in nonstandard formats.
You can find the descriptions and extensions of the Filters items using this code in the [Immediate Window] (keyboard shortcut Ctrl+g):
?oFD.Filters(1).Description:?oFD.Filters(1).ExtensionsJust change
Excel Workbook
*.xlsxYou can also see the list in the Locals window if you prefer that (View>Locals Window in the VBE).
To set the default filter, simply write a code like this: oFD.FilterIndex = 1. You’ll want to replace the integer
That’s about all the filter customization you can do with the VBA Save As dialog box.
The Initial View
Unlike the string properties, Enum properties have a distinct set of options. The InitialView property is one of those Enum properties. The operating system can only display information in so many ways, so the number of selectable options for setting the InitialView property is limited. If you have Intellisense turned on, when you type oFD.InitialView = ..., you should get a dropdown list of possible default views for the dialog itself. Options include things like large icons, thumbnails, and details. They’re generally self-explanatory.
| List of InitialView Styles |
|---|
| msoFileDialogViewDetails |
| msoFileDialogViewLargeIcons |
| msoFileDialogViewList |
| msoFileDialogViewPreview |
| msoFileDialogViewProperties |
| msoFileDialogViewSmallIcons |
| msoFileDialogViewThumbnail |
| msoFileDialogViewTiles |
| msoFileDialogViewWebView |
The reason they are called Enum is because they are enumerated. You can actually assign them as an integer instead of a long name like msoFileDialogViewDetails or msoFileDialogViewThumbnail. It’s much easier for people to understand and remember the name rather than the corresponding “enumeration” number. The numbers often are not simply listed as 1,2,3, either, which means you cannot always guess the correct Enum. Using Intellisense and the predefined strings makes using enumerated properties much easier.
Just take note that the initial view property is not the most reliable, and you might not get the view you want. The OS might override your decision, so don’t get frustrated if your initial view does not match what you specified.
The Methods
Just like the other FileDialogs, the msoFileDialogSaveAs box has two methods: Show and Execute. The Show method draws the window with the preset properties you specified earlier. When you show the dialog, you won’t be able to do anything with other parts of Excel or VBA (there is no modal version for FileDialogs).
Once you display the dialog, the user can select the file location and name and click the Save button. The Save button won’t do anything until call the .Execute method, though.
Execute
Pressing the Save button in the Save As FileDialog will not actually save the file. It only populates the SelectedItems collection with the filename and filepath the user selected as a string (including the automatically appended extension). To save the file, you need to call the Execute method.
Since there is no way to choose nonstandard files for users, as long as resources are not locked, this should be a pretty smooth process. Even if a user types myFile.special, the attempt to create a .special file extension will be moot: VBA will just add .txt or .xlsx to the end, creating myFile.special.xlsx.
Execute will save the active workbook as the filename and type selected by the user in the dialog box, so you will need to know which one is active before implementing this method. Keep in mind that this is a Save As dialog, so the currently open version will be saved as a copy with the new filename.
Saving A File Other Than the Active Workbook
It is quite common to save something other than the active workbook. If you’ve been processing files in VBA but not actually opening them in Excel, such as a text file loaded via FSO OpenTextFile, you might only run the Save As dialog to give users a visually-appealing and interactive way to select the file destination.
In that case, you can access the user’s choice through the SelectedItems collection, which will only have one item (unlike the Open dialog that could have multiple items). In the Immediate window, type the following to retrieve the filepath string:
?oFD.SelectedItems(1)You would just save your file with that string name, like you would for any other VBA File I/O trick.
User Cancellations
On the other hand, it is possible that the user will press the Cancel button instead of the Execute button. In that case, our SelectedItems collection will be empty and you cannot execute anything. You won’t get an error, but you won’t get a saved file, either.
In that case, it is practical to check whether the Cancel button was selected. Remember that .Show is a method or function, and it always returns some value to the VBA program. That means you can check it like so:
If oFD.Show <> 0 Then
'your code for handling saves goes here
End IfIf the user presses Cancel, the method returns the integer zero and you can handle that case. Perhaps you can send out a MsgBox to ask them to confirm that they do not want to save.
Persistence
As discussed in our msoFileDialogOpen tutorial, only one FileDialog object can live in an instance of Excel. If you change the type (like msoFileDialogSaveAs to msoFileDialogOpen), all of your future FileDialogs will change to Open types, even if you have different objects in your macro. For example, this code will result in both
Dim oFD As FileDialog
Dim oFD1 As FileDialog
Set oFD = Application.FileDialog(msoFileDialogSaveAs)
Set oFD1 = Application.FileDialog(msoFileDialogOpen)Don’t believe me? Pause your macro write after these lines and type ?oFD.DialogType:?oFD1.DialogType in your immediate window. You’ll see they’re both reported as the same dialog type, even though we set the different objects to different types. Moreover, even if the macro stops, the last FileDialog will persist until the entire Excel application is restarted. Thus, if you originally set the default filter to be .xlam but ran a completely separate macro later and wanted .csv, you would need to reset the filter index in the second macro.
Just keep this in mind when using FileDialogs - it can lead to many frustrating bugs and user complaints if you forget about persistence.
Resetting the Dialog
Even though the FileDialog is persistent while the application is alive, you can still reset the dialog if you’ve made significant changes to the defaults by reassigning the FileDialog to a different type and then back again.
Set oFD = Application.FileDialog(msoFileDialogSaveAs)
Set oFD = Application.FileDialog(msoFileDialogOpen)
Set oFD = Application.FileDialog(msoFileDialogSaveAs)The third line here will create the FileDialog in its default state. It isn’t a great workaround, and it might not even be intentional on Microsoft’s part, so I suggest not relying on this solution. Rebuilding explicitly is a much safer route.
A Fully Implemented Example
Although the filters are not as customizable with the Open FileDialog, the Save As dialog still offers some nice customization. Sometimes it’s important to give the user a choice for where to save files via a GUI FileDialog rather than simply saving it outright or using a clumsy VBA InputBox.
If you want really customizable features, you’ll have to build an entire UserForm scratch. It is an arduous process to build a beautiful GUI, though, so why not just use the tools Microsoft has already given us?
To get you started, here is the code block we’ve pieced together throughout this tutorial. After the macro, you’ll see a screenshot of what appears once you call the Show method. Note that the target workbook here is
Sub save_as_dialog_final()
Dim oFD As FileDialog
Set oFD = Application.FileDialog(msoFileDialogSaveAs)
oFD.Title = "Choose a Location and Name of the File to Save This File"
oFD.ButtonName = "Click to S&ave"
'oFD.InitialFileName = "C:\Users\" 'sets the folder
oFD.InitialFileName = "C:\Users\myFile" 'sets the folder and populates the file name box
oFD.FilterIndex = 2 'sets .xlsm as initial filetype
oFD.InitialView = msoFileDialogViewLargeIcons
If oFD.Show <> 0 Then
'code for when the user provides information for saving
If ActiveWorkbook.Name = "dailyReport.xlsx" Then
oFD.Execute
Else
Workbooks("dailyReport.xlsx").SaveAs oFD.SelectedItems(1)
End If
Else
'code for when the user presses cancel
End If
End Sub
The VBA Save As FileDialog customized to the specifications in the macro above
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
