Set the "save as type" field when using Application.FileDialog (msoFileDialogSaveAs) using MSAccess

Question

I was looking for everything to do this.

I want to open the Save As dialog box so that the user can select the location to save the file. But I want the "Save as type" field to be pre-configured with a "comma separated value File (* .csv)"

The problem is that the Filter method does not 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 extension .csv and then open it in excel, I get a "The file you are trying to open xxx.csv is in a different format than indicated by the file extension ..." message. The file works correctly, but.

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!

Answer 1

As said, the <<20> help msoFileDialogSaveAs not supported.

You can force the CSV extension to FileName when unloading the dialog;

 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 doesn't like your CSV, check to see if there are any values ​​you need to quote to avoid new lines /"(http://stackoverflow.com/questions/566052/can-you-encode-cr-lf- in-into-csv files)

And instead of this template

 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 

Answer 2

Later, 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 is well known, you can not directly set filters on msoFileDialogSaveAs. However, if you set the InitialFileName to "* .ext", this will force this extension. The filter will still say “All files,” however it will not show files if they don’t have the extension you provided.

Result

If you delete "* .ext" and just write "test", for example, the resulting file name will be "test.ext", so it actually forces this extension.

This is not ideal, but very simple and achieves the desired result without resorting to API calls for those who are less experienced with code.

Warning

This only works for one extension at a time, for example. "* .csv". If you need to filter out several extensions, for example. images, then you have to resort to using API calls.

Answer 3

As Mike wrote and the link he suggested; to select the default filter 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 

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 (open a dialog and count the list to your extension) or change the collection of save filters as described on the page related to msdn. Filters cannot be changed in the filedialog instance, only before that with the filedialogfilters object via Application.FileDialog (msoFileDialogSaveAs). Filters Then they are available in the instance.