Preset the "save as type" field while using Application.FileDialog(msoFileDialogSaveAs) with MSAccess

Question

I searched all over for a way to do this.

I want to open a Save As dialog box so the user can choose the location to save a file. But, I want the "Save as type" field to be preset with "comma seperated value File (*.csv)"

The problem is the "Filter" methode does not seem to work with "msoFileDialogSaveAs". Is it possible to preset the file type using "Application.FileDialog(msoFileDialogSaveAs)"?

At the moment, if I save the file with the .csv extension and then open it in excel, I get the "The file you are trying to open xxx.csv is in a different format than specified by the file extension ..." message. The file works correctly though.

 With Application.FileDialog(msoFileDialogSaveAs)
        .Title = "xxx"
        .AllowMultiSelect = False
        .InitialFileName = "xxx.csv"
        '.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
        result = .Show
        If (result <> 0) Then
            ' create file
            FileName = Trim(.SelectedItems.Item(1))
            fnum = FreeFile
            Open FileName For Output As fnum


            ' Write the csv data from form record set
            For Each fld In rs.Fields
               str = str & fld.Name & ", "
            Next

           ' Write header line
           str = Left(str, Len(str) - 2)   ' remove last semi colon and space
           Print #fnum, str
           str = ""

          ' Write each row of data
           rs.MoveFirst
          Do While Not rs.EOF             
            For i = 0 To 40
                str = str & rs(i) & ", "    ' write each field seperated by a semi colon
            Next i
            str = Left(str, Len(str) - 2)   ' remove last semi colon and space
            Print #fnum, str
            str = ""
            rs.MoveNext
           Loop

        ' close file
        Close #fnum
        End If
  End With

Than You!

Byplay

As you say msoFileDialogSaveAs is not properly supported in Access. If your saving a file that has a .csv extension and excel is giving you that warning, its what's in the file thats causing the problem, not its name. – Girandole

I will look at my file formatting to make sure it respect csv requirement. It would still be nice to prevent the user from saving this file with no extension. – Byplay

Can't you use the .Filters method ? What is the code that actually saves the file ? – Dendriform

.Filter gives me Invalid use of property. msoFileDialogSaveAs does not seem to support this property. – Byplay

Previous comment should read: .Filter gives me: "Method or data member not found". msoFileDialogSaveAs does not seem to support this property. – Byplay

Answer 1

As stated he FileDialog help states msoFileDialogSaveAs is not supported.

You can force a CSV extension on FileName when the dialog unloads;

FileName = getCSVName(FileName)
...
Function getCSVName(fileName As String) As String
   Dim pos As Long
   pos = InStrRev(fileName, ".")
   If (pos > 0) Then
       fileName = Left$(fileName, pos - 1)
   End If
   getCSVName = fileName & ".CSV"
End Function

If excel isn't liking your CSV, check if there are any values you need to quote to escape newlines/" (https://mcmap.net/q/194625/-can-you-encode-cr-lf-in-into-csv-files)

And instead of this pattern;

For i = 0 To 40
   str = str & rs(i) & ", "    ' write each field seperated by a semi colon
Next i
str = Left(str, Len(str) - 2)   ' remove last semi colon and space

you can;

dim delimiter as string
...
For i = 0 To 40
   str = str & delimiter & rs(i)  ' write each field seperated by a semi colon
   delimiter = ","
Next 

Girandole

Thank you, I just thought of this solution and was looking for a way to replace the extension with regex. So this is very helpful – Byplay

For the excel not liking my csv: the problem was my file started with ID witch is indicate a SYLF file apparently. – Byplay

Answer 2

Late as usual but hopefully a better solution...

Public Function GetSaveFilename() As String

    Dim Dialog As FileDialog: Set Dialog = Application.FileDialog(msoFileDialogSaveAs)
    With Dialog
        .InitialFileName = CurrentProject.Path & "\*.ext"
        .FilterIndex = 2
        .Title = "Save As"
        If .Show <> 0 Then
            GetSaveFilename = .SelectedItems(1)
        End If
    End With
End Function

How it works?

As it is well know you can not directly set filters on msoFileDialogSaveAs. However if you set the InitialFileName to "*.ext" then it will force that extension. The filter will still say "All Files" however it will not show files unless they have the extension you provided.

The Result

If you erase "*.ext" and just write "test" for example the resulting filename will be "test.ext", so it actually forces that extension.

It's not perfect but it is very simple and achieves the desired result without resorting to API calls for those less experienced with code.

Caveats

This only works for a single extension at a time e.g. "*.csv". If you need to filter multiple extensions e.g. images then you will have to resort to using API calls.

Perspicacity

This worked for me, but then oddly, it started throwing an error 5 on the line for .FilterIndex = 2 so I removed it with seemingly no consequence. Late comment to a late answer... – Szabo

Answer 3

As Mike wrote and from the link he proposed; to choose the filter you want by default, you can:

Sub Main()
    Debug.Print userFileSaveDialog("unicode", "*.txt")
End Sub

Function userFileSaveDialog(iFilter As String, iExtension As String)

    With Application.FileDialog(msoFileDialogSaveAs)
        Dim aFilterIndex As Long: aFilterIndex = 0&

        For aFilterIndex = 1& To .Filters.Count
            If (InStr(LCase(.Filters(aFilterIndex).Description), LCase(iFilter)) > 0) _
                And (LCase(.Filters(aFilterIndex).Extensions) = LCase(iExtension)) Then

                .FilterIndex = aFilterIndex
                Exit For

            End If
        Next aFilterIndex

        If CBool(.Show) Then
            userFileSaveDialog = .SelectedItems(.SelectedItems.Count)
        Else
            End
        End If
    End With

End Function

Sollie

The only filter extention I get using this itteration is ".". Where are these defined? And then still, where can I update these to add a custom extention, if one is missing? Is this a system setting? – Vesture

@Pete; those are the default extensions defined when you do a save as... with excel. There are 27 different extensions in excel 2010 including ("unicode", "*.txt"). If you want to propose a custom extension to the user you could do that – Sollie

This is the better answer, IMHO: I used it to make sure the default file type is Excel 97-2003 (*.xls) regardless of the macro being run in 2003 or 2016 (our office is finally updating but it's a gradual conversion and some other internal tools expect only .xls.) – Lampert

There is a small mistake in the code: line If CBool(.Show) Then userFileSaveDialog = .SelectedItems(.SelectedItems.Count) Else End End If I would replace the End before End If with userFileSaveDialog = vbNullString – Astute

To get only a filter see #20272500 but it doesn't work for PowerPoint, only Excel? – Astute

Answer 4

http://msdn.microsoft.com/en-us/library/office/aa219834(v=office.11).aspx

Use filterindex to select the desired extension from the default list (launch the dialog and count down the list to your extension), or modify the saveas filter collection as documented in the page linked at msdn. The filters can't be changed within the filedialog instance, only prior to that with a filedialogfilters object via Application.FileDialog(msoFileDialogSaveAs).Filters. They are then available within the instance.

Coreen

This does not seem to work, as as stated in the documentation, filters are reset for each new dialog. Please expand. – Madaih

Answer 5

Dim FileDialogObj As FileDialog

'1.0 Open File Dialog
Set FileDialogObj = Application.FileDialog(msoFileDialogSaveAs)
With FileDialogObj
   .InitialFileName = "C:\"
   .Filters.Item 3  '****This is to set File Dialog Save As to CSV ******
   .Title = "Save As"
   .AllowMultiSelect = False
End With

Lunchroom

This doesn't seem to work (tested with PPT) – Astute