Use the built-in dialogs in Excel

It's not always necessary to invent everything on your own when you can use something that already exists. You have access to most of the built-in dialogs in Excel and the other applications in Office. If you want to let the user decide where to save a workbook, you can display the built-in dialog Save as like this:

Application.Dialogs(xlDialogSaveAs).Show

To get a list over all accessible dialogs in Excel, use the Object browser. To display it, press F2 while you have a module activated. Select Excel as library and look for the constants beginning with xlDialog....

If you just want to retrieve a filename from the user that you want to use later to open or save a workbook, you can use the GetOpenFilename or GetSaveAsFilename methods. Both methods displays their respective dialogs, but the don't open or save the file when the user confirms the dialog. The methods instead returns the complete filename to the file the user wants to open or save.

FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
    1, "Custom Dialog Title", , False)

FullFileName = Application.GetSaveAsFilename("DefaultFilename.xls", _
    "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")

The result can be used any way you want to. You can later open a selected workbook like this:

Workbooks.Open FullFileName

Or you can save a workbook like this:

ActiveWorkbook.SaveAs FullFileName

When the user has confirmed the FileOpen dialog or the FileSaveAs dialog, he/she might also have changed the active or current folder, so I often use these two methods as a simple approach to let the user select a folder. You can find another way of letting the user select a folder in the Files and folders section.

Document last updated 2000-02-05 12:47:20

User comments:

Bundi from Sweden wrote (2006-10-13 12:37:14 CET):

Re: Save as unicode txt
Hi again Ole!

Thanks for your quick reply (as always!)

Yes, this is pretty much what I was looking for, but when the dialog window opens, the button says "Open" and not "Save"?
So I am being prompted to open a txt file instead of saving the file as txt?
Any suggestion on how to change this?

Best regards, Bundi

Ole P. from Norway wrote (2006-10-13 00:01:43 CET):

Re: Save as unicode txt
This might be what you are looking for:

FullFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt,All Files (*.*),*.*", _
1, "Custom Dialog Title", , False)
If Len(FullFileName) < 6 Then Exit Sub ' no file selected
ActiveWorkbook.SaveAs Filename:=FullFileName, FileFormat:=xlUnicodeText

Bundi from Sweden wrote (2006-10-12 12:09:37 CET):

Save as unicode txt
Hi Ole!

I am trying to use this built in save dialog "Application.Dialogs(xlDialogSaveAs).Show"
But I would like to force the user so that they can only save the file in question as a unicode txt file.
I have seen it somewhere but sadly forgot how to accomplish this.

Thanks in advance, Bundi

Andy Coates from Oxford, UK wrote (2006-01-19 15:56:46 CET):

Specifying folder
Nicolas,

Simply put the line:

ChDir "G:\MyFolder\MySubFolder"

before the Application.GetOpenFilename line.

Hope this helps!

Nicolas from Zürich (Switzerland) wrote (2005-04-29 13:40:56 CET):

GotOpenFilename :-)
Hi Ole!

Sorry for my question-answer!

I found how to get open the "Open"-dialog window directly at a chosen folder:

Sub FetchFile()
Dim path as string, chosen_filename as Variant

path = "C:\MyFolder\MySubFolder"
ChDrive path
ChDir path
chosen_filename =Application.GetOpenFilename('whatever format you want')
if chosen_filename = false then exit sub
Workbooks.Open Filename:= chosen_filename

End sub

I hope it could help others who get problems with that.

Sorry again.

Nicolas from Zürich (Switzerland) wrote (2005-04-29 11:03:01 CET):

GetOpenFilename... from a customized folder?
Hi Ole,

I wonder if it's possible to use the GetOpenFilename method but by precising in which folder I want to choose a file, so that when the dialog opens, it's in the chosen folder.

Thanks in advance for your help.