Excel API Save As Dialog box issue

Question

The Excel 2003 API has a GetSaveAsFilename method that pops up the Save As dialog. This takes in various parameters, including the file type (xls, txt, csv,...), something like the following:

GetSaveAsFilename(Missing.Value,
                  "Microsoft Office Excel Workbook (*.xls), *.xls",
                  1,
                  Missing.Value,
                  Missing.Value);

My issue is, I see only "*.xls" in the dialog File type dropdown. If I skip the file type part (the 2nd parameter), I see just "All Files (*.*)" in it. How can I get to see the normal list that contains all possible file types without creating a long string of all file types and passing it to the method?

Answer 1

You want Application.Dialogs(xlDialogSaveAs).Show.

Sub showdialog()
    Dim name as string
    name = "test"
    Application.Dialogs(xlDialogSaveAs).Show name 'drop the name parameter 
                                                  'if you don't want to provide 
                                                  'a default name
End Sub

This will just allow the user to save the file. This won't allow you to get the name they selected directly, though. It will return a boolean value, true meaning they clicked OK. Since it's the Save dialog, though, you could see if they clicked OK then check the current filename. This would basically tell you what they saved the file as.

Answer 2

Here's a link that may be able to help. The relevant post is the third item. It's not an ideal solution (which would be some constant), but it could be a way for you to programmatically loop through the available filters and build up a string to use in GetSaveAsFilename.

Here's the relevant part of the code:

Sub Main()

'Declare a variable as a FileDialogFilters collection.
Dim fdfs As FileDialogFilters

'Declare a variable as a FileDialogFilter object.
Dim fdf As FileDialogFilter

'Set the FileDialogFilters collection variable to
'the FileDialogFilters collection of the SaveAs dialog box.
Set fdfs = Application.FileDialog(msoFileDialogSaveAs).Filters

'Iterate through the description and extensions of each
'default filter in the SaveAs dialog box.
For Each fdf In fdfs

    'Display the description of filters that include
    Debug.Print fdf.Description & " (" & fdf.Extensions & ")," & fdf.Extensions

Next fdf

End Sub

Hope this helps!