Excel VBA Bug : GetSaveAsFilename Method doesn't work

[Edwin Tam]

In Excel 2004 VBA, the GetSaveAsFilename method, if try to set the FileFilter
arguement for the file types, the desired result will never come out.

The file type dropdown list will forever show all those default file types.

I haven't re-tested this on other versions of Mac Excel yet. But according
to my memory, this bug seemed to exist since some versions ago.

The GetSaveAsFilename is one of the fundamental methods programmers use to
obtain filename input from users. However, this method is handicapped on the
Mac.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Natasha - Microsoft]

Thank you for your feedback, Edwin. This information has been noted
with the company. We appreciate your help in making our products
better.

Thank you,
Natasha Bishop
Microsoft

 

[wayne ingalls]

In Excel 2004 VBA, the GetSaveAsFilename method, if try to set the FileFilter
arguement for the file types, the desired result will never come out.

Sorry I'm a bit late to respond. I needed to use this feature with a
macro I wrote recently, but if you try to follow the Excel documentation
for the Mac you won't get very far. This isn't the only function that's
incorrectly documented in Excel, but I don't hold out much hope that it
will ever be corrected.

My need was to save a file as comma separated values. If you want to
save in a different format, the easiest way to learn the correct syntax
would be to create a command button on a spreadsheet, then use the macro
recorder to follow your mouse clicks while you save in the desired
format. Then open the VBA editor and read the code to see the file
filter syntax. Then you can delete the recorded macro and write your own.

I'll give you my code for a button that saves a worksheet in CSV format,
compatible with both PC and Mac format. You may have to fix any wordwrap
problems with my newsreader. Feel free to e-me at wingalls at
frontiernet dot net if you have other questions or the code doesn't look
readable.

-wayne

- - - - - - -

Private Function SaveAsCSV(myName As String)
' need variable to hold file name
Dim fName
Dim intLength As Integer
' suggest that the user change the file name
MsgBox "Please change the file name before saving in CSV format"

' different code depending on operating system

' this section is for PC users
#If Win32 Then
fName = Application.GetSaveAsFilename(fileFilter:="CSV (*.csv), *.csv")
If fName <> False Then
Worksheets(myName).SaveAs FileName:=fName, FileFormat:=xlCSV
End If

' this section is for Mac users
#ElseIf Mac Then
' this section is for Mac users
fName = Application.GetSaveAsFilename
' tweak the name for mac users
If fName <> False Then
' if there's ".xls" or ".xlt" at the end of the name, remove it
If (Right(fName, 4) = ".xls") Or (Right(fName, 4) = ".xlt") Then
intLength = Len(fName)
fName = Mid(fName, 1, intLength - 4)
End If
' now add the ".csv" extension to the name
fName = fName & ".csv"
Worksheets(myName).SaveAs FileName:=fName, FileFormat:=xlCSV
End If
#End If

' let the user know that there will be a message from excel asking if we
should save changes. NO is ok.
MsgBox "When you close the file, Excel will ask you if you want to save
changes to the file." _
& "You may click the button for Don't Save Changes."

End Function

 

[wayne ingalls]

In Excel 2004 VBA, the GetSaveAsFilename method, if try to set the FileFilter
arguement for the file types, the desired result will never come out.

Sorry I'm a bit late to respond. I needed to use this feature with a
macro I wrote recently, but if you try to follow the Excel documentation
for the Mac you won't get very far. This isn't the only function that's
incorrectly documented in Excel, but I don't hold out much hope that it
will ever be corrected.

My need was to save a file as comma separated values. If you want to
save in a different format, the easiest way to learn the correct syntax
would be to create a command button on a spreadsheet, then use the macro
recorder to follow your mouse clicks while you save in the desired
format. Then open the VBA editor and read the code to see the file
filter syntax. Then you can delete the recorded macro and write your own.

I'll give you my code for a button that saves a worksheet in CSV format,
compatible with both PC and Mac format. You may have to fix any wordwrap
problems with my newsreader. Feel free to e-me at wingalls at
frontiernet dot net if you have other questions or the code doesn't look
readable.

-wayne

- - - - - - -

Private Function SaveAsCSV(myName As String)
' need variable to hold file name
Dim fName
Dim intLength As Integer
' suggest that the user change the file name
MsgBox "Please change the file name before saving in CSV format"

' different code depending on operating system

' this section is for PC users
#If Win32 Then
fName = Application.GetSaveAsFilename(fileFilter:="CSV (*.csv), *.csv")
If fName <> False Then
Worksheets(myName).SaveAs FileName:=fName, FileFormat:=xlCSV
End If

' this section is for Mac users
#ElseIf Mac Then
' this section is for Mac users
fName = Application.GetSaveAsFilename
' tweak the name for mac users
If fName <> False Then
' if there's ".xls" or ".xlt" at the end of the name, remove it
If (Right(fName, 4) = ".xls") Or (Right(fName, 4) = ".xlt") Then
intLength = Len(fName)
fName = Mid(fName, 1, intLength - 4)
End If
' now add the ".csv" extension to the name
fName = fName & ".csv"
Worksheets(myName).SaveAs FileName:=fName, FileFormat:=xlCSV
End If
#End If

' let the user know that there will be a message from excel asking if we
should save changes. NO is ok.
MsgBox "When you close the file, Excel will ask you if you want to save
changes to the file." _
& "You may click the button for Don't Save Changes."

End Function