Application.FileDialog vs GetOpenFilename: What is the difference?

Question

I'm trying to teach myself VBA during the short holiday break. For my first module, I want to create a module that does the following:

1. Displays dialog box. Prompts user to select CSV files.
2. Open the CSV files via loop.
3. Summarize the CSV files based from their data.

Anyway, for number1, I search and found two methods in examples online: Application.FileDialog and GetOpenFilename. Hm, I was wondering, what is the difference between the two (limitation, capabilities, speed, etc.) Is there a significant advantage of one method over another?

Thanks.

Answer 1

Application.FileDialog is an object. GetOpenFilename is a property. If you call GetOpenFilename, Excel owns the dialog object, configures it, displays it, and gives you the return value. If you use Application.FileDialog, you configure it, display it, and get the return value from the object. Outside of that, they're exactly the same - it's just a matter of who owns and controls the underlying FileDialog.

Answer 2

Application.FileDialog is umbrella property to provide you with several types of standard file dialogs: (1) file picker, (2) folder picker, (3) open file dialog and (4) "Save As" dialog. After getting FileDialog object, you can customize it further and call Show() (in some cases followed by Execute()) to display it and get user action.

Application.GetOpenFilename method displays dialog for getting file name (also see cases 1, 3, 4 above) allowing you to fast-preset selected set of its properties, namely file filter, index of initial file filter, dialog title, action button text (e.g. "Open" or "Save") and option, whether multiple files can be selected at once (MultiSelect). But you have no control beyond these presets.

So basically former one gives you more control over the dialog window while the latter is "faster" to adopt in standard simple one-off scenarios.

Sticking with fast approach, you might want to prefer GetOpenFilename(), but if you wish to create library for larger application which will for example always use fie dialog with some custom standards (custom file filters or custom window custom title specific for your application), you might prefer custom method which tweaks FileDialog according to your standards and displays it.

Also please note that both above methods present you with dialog customized for Microsoft Office. With a bit of googling you can find your way to use standard dialogs from Microsoft Windows, for example folder picker dialog.

Answer 3

Here are differences I have sorted out, starting from the idea FileDialog gives an object to set up and retrieve results from, while GetOpenFileName gives a one-and-done function.

a. File Dialog has more options. These include:

• pre-setting a filename for when the dialog opens (InitialFileName property)
• changing the names of the button (ButtonName property)
• changing how the files are shown (InitialView property)
• an Execute method to actually open or save the file that's been selected.

b. GetOpenFilename goes with GetSaveAsFilename. The difference between these two is also confusing at first, until you realize that you can't change the button caption (unless possibly on a Mac). That means you can change the title, but the button will still say "Open" or "Save". If you are picking files for other reasons, FileDialog lets you set the button to a custom string.

c. File Dialog Types. While FileDialog leaves more to the user, it has basic setups by use of the single fileDialogType argument:

• set fd = Application.FileDialog(msoFileDialogFilePicker)
• set fd = Application.FileDialog(msoFileDialogFileOpen)
• set fd = Application.FileDialog(msoFileDialogSaveAs)

Each gets you the same object (see the next bullet), but with different properties. Most important is the DialogType property, which is then read-only, and can be changed only via a new set statement. This property determines what happens with .Execute: open, save, or for msoFileDialogPicker, an error. Other properties are set to match the initializing fileDialogType argument, but can generally be changed before you call .Show.

d. File Dialog quirks. With the FileDialog object, it turns out that you have just one per application. That means if you try to think ahead with set fdo = Application.FileDialog(msoFileDialogOpen) to open files and then set fdsa = Application.FileDialog(msoFileDialogSaveAs) to save files, hoping to keep both for later use, you'll soon learn that fdo and fdsa refer to the same object, now with the msoFileDialogSaveAs settings. The lesson is to set the parameters just before you use it.

e.) Getting the results. For the GetOpenFilename and GetSaveAsFilename methods, since they operate as functions, you get the selection as a return value. The return value may be a single selection, an array, or False if the user clicked Cancel. For the FileDialog object, in contrast, you get results through a SelectedItems collection as a property of the object. If a user clicked Cancel, the count will be 0, and the .Show method also returns -1. The latter is used in the pattern if fd.Show then fd.Execute, often in a with block.

Based on these, I think I'll stick with the file dialog. It can also pick folders, so it's probably a good one to be familiar with.