SaveAs file dialog and filters

[Smoothcat74]

Hi all,

I have the following code which is an effort to create a Save As file dialog. It works perfectly, except when I try to add the filters to ensure the user saves the document as a .xls. I get a runtime error stating "Object doesn't support this property or method." Does msoFileDialogSaveAs not support filters? Is there a workaround for this? Thanks in advance!

Sub ExcelExport()
Dim SaveAs As FileDialog

Set SaveAs = Application.FileDialog(msoFileDialogSaveAs)

Dim FileName As Variant

With SaveAs
.AllowMultiSelect = False
.Title = "Save Export File"
.ButtonName = "Save"
.InitialFileName = "Productivity Report.xls"
With .Filters
.Clear
.Add "Excel Documents", "*.xls"
End With



If .Show = -1 Then
    For Each FileName In .SelectedItems
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProdReport", FileName, True
      MsgBox "File successfully exported."
    Next FileName
    Else
    MsgBox "File not exported."
End If
End With

 

[dick606]

I wonder if you could just popup an input box and get the user to input the name of the file they want in that? That way, you could assign the file extension yourself and not even let them know that you are doing it. Take it out of their hands. That is one way to do it I suppose, which is the way I would really go if I were you.