Weird bug in Excel’s Application.GetSaveAsFileName() function on Vista and 7

I have found a bug and a strange problem while using Excel’s native Application.GetSaveAsFileName() function.

First to the bug.

Using Excel 2007 on top of Windows 7, this dialogue cleared the initial name I provided (obtained manipulating the current workbook’s name) while it was working fine on Windows XP. A quick test on Vista made me aware that this was also occurring on that OS.

Obviously, by coincidence, the answer:

• if the extension of the filemane you provide is the same as the last one in the filter criteria, the name is not cleared
• if you do not provide an extension on the filename, the name is also not cleared
• if you supply an extension that is either not in the list of wildcard extensions or not the last one, the name is cleared (in XP the full name would be enclosed in double quotes)

To replicate this behaviour do the following:

1. In a virgin workbook open the code pane for ThisWorkbook
2. Paste the following code:
   Public Sub TestGetSaveAsFileName()
Dim sName As String
Dim sFilter As String
sFilter = "Excel Macro Enabled filter,*.xlsm;*.xlsb;*.xls,All Files,*.*"
sName = "File name (no ext) (filter is xlsm & xlsb)"
Debug.Print "1) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
sName = "File name (xlsm) (filter is xlsm & xlsb).xlsm"
Debug.Print "2) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
sName = "File name (xlsb) (filter is xlsm & xlsb).xlsb"
Debug.Print "3) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
sName = "File name (xls) (filter is xlsm & xlsb).xls"
Debug.Print "4) " & Application.GetSaveAsFilename(sName, sFilter, 1, "Test")
End Sub
3. Run the code and watch the file’s name in the dialogue.

I presume this bug is not caused by Excel 2007 since the behaviour is consistent on XP but not on Vista nor on Windows 7, so for me this is a Vista/7 bug. I ran a quick test with Excel 2003 on Vista and 7 and there also the names disappear when the above conditions are met so this confirms it.

Now the strange behaviour.

According to the documentation (Excel 2000 up to Excel 2010) the GetSaveAsFileName() defines the following for the FileFilter parameter:

This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: “Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla”.

To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, “Visual Basic Files (*.bas; *.txt),*.bas;*.txt”.

I have found that this is not true. If you use a file filter in the dialogue’s label, this file filter gets priority over the real file filter(s). For example

“Text Files (*.pdf), *.txt;*.log, Add-In Files (*.doc), *.xla”.

will show a dialogue filtering on either “*.pdf” (from first label) instead of “*.txt” or “*.log” or on “*.doc” (from second label) instead of “*.xla”. Not convinced? Try by yourself! Solution? Do not use wildcard extensions in the label of the filter criteria.

On Microsoft XP and 7 the wildcard extension will automatically be appended to the filter’s label. However, on Vista the wildcard extensions are never displayed, so if you open the dialogue then you can only guess what wildcard extension is being applied. I have tried not hiding known extensions but on Vista I was nevertheless unable to see what wildcard extensions were associated to each entry.

A programmer friend of mine confirms that the bug and the strange behaviour are caused by the Common Dialog control of Windows Vista and Windows 7 which is used by the Excel function. He also mentioned a third one (which I have not seen yet, could related to a localised version of Windows) being that under circumstances the wildcard extension is appended to the label and therefore appears twice on the entry. For instance this filter “Text Files (*.txt),*.txt” would display as “Text Files (*.txt) (*.txt)”. This strengthens the recommendation for not putting any wildcard expressions in the label.

To avoid the bug mentioned in the beginning of my post, i.e. the filename that disappears when not with the correct extension, I recommend splitting filter definitions with multiple wildcard expressions into many single definitions with only one wildcard extension each.

This would be incorrect:

“Excel Macro Enabled files,*.xlsm;*.xlsb;*.xls,All Files,*.*”

This would be correct:

“Excel Macro Enabled document,*.xlsm,Excel Macro Binary document,*.xlsb,Excel 2003 document,*.xls,All Files,*.*”

It’s a little cumbersome but effective. Obviously, if you use filter preselection on the dialog, make sure the correct one is selected when setting the filename.

Dutch