File and Folder Dialoges VBA

Jun 07, 2015 by azurous in Excel

You have probably worked with file and folder dialogues before.

Selecting a file to open
choosing a path to save the current file
choosing a directory path

Although it would seem that the dialog itself does the saving and opening, but most dialogues actually don’t do any of that. In general they return two types of variables:

False: If the user chooses to cancel the dialog.
Path: If the user selects a file or folder, then the selected path is returned

Jump To:

Folder Dialog
Save File Dialog
Open File Dialog
Saving the current workbook using a file dialog
Opening a workbook using a file dialog

–

Contents

Folder Dialog:

Using the Folder Dialog, a dialog will appear and prompt the user to select a directory path:

For more information about the folder dialog please see VBA Folder Dialog.

–

Save File Dialog:

Using the save file dialog, a dialog will appear and prompt the user to select a location to save the file:

Keep in mind that this dialog does not actually save anything. It only returns the path selected by the user to save the file. With the file path you can then decide what you want to do with the file. For more information the save file dialog please see the links below:

–

Open File Dialog:

Using the open file dialog, a dialog will appear and prompt the user to select a file to Open:

Keep in mind that this dialog does not actually open anything. It only returns the path of the selected file or files by the user. With the file path you can then decide what you want to do with the file. For more information about the open file dialog please see Excel VBA Open File Dialog.

Saving the current workbook using a file dialog:

Using the code below below a save file dialog will open. In this case should the user select a save path, the function will not return a string with the path of the file select. It will automaticaly save the current workbook. If the user cancels the dialog, the value false is returned.

Dim varPath As Variant
varPath = Application.Dialogs(xlDialogSaveAs).Show

‘if the user cancels the dialog varPath is assigned the value False
If varPath = False Then
‘your code
End If

–

Opening a workbook using a file dialog:

Similar to the previous case, only this time a workbook is opened through the dialog:

Dim varPath As Variant
varPath = Application.Dialogs(xlDialogOpen).Show

‘varPath will be false if the user cancels the dialog
If varPath = False Then
‘add code
End If