vba 使用 Application.FileDialog(msoFileDialogSaveAs) 和 MSAccess 时预设“另存为类型”字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7770030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Preset the "save as type" field while using Application.FileDialog(msoFileDialogSaveAs) with MSAccess
提问by pec
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)"
我想打开一个另存为对话框，以便用户可以选择保存文件的位置。但是，我希望“另存为类型”字段预设为“逗号分隔值文件 (*.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)"?
问题是“过滤器”方法似乎不适用于“msoFileDialogSaveAs”。是否可以使用“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.
目前，如果我使用 .csv 扩展名保存文件，然后在 excel 中打开它，我会得到“您尝试打开 xxx.csv 的文件的格式与文件扩展名指定的格式不同......”信息。该文件虽然工作正常。
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!
比你！
采纳答案by Alex K.
As stated he FileDialoghelp states msoFileDialogSaveAsis not supported.
如前所述，他的FileDialog帮助状态msoFileDialogSaveAs不受支持。
You can force a CSV extension on FileNamewhen the dialog unloads;
您可以FileName在对话框卸载时强制启用 CSV 扩展；
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/" (http://stackoverflow.com/questions/566052/can-you-encode-cr-lf-in-into-csv-files)
如果 excel 不喜欢您的 CSV，请检查您是否需要引用任何值来转义换行符/”（http://stackoverflow.com/questions/566052/can-you-encode-cr-lf-in-into -csv-文件）
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
回答by Dave Williams
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 notdirectly 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.
众所周知，您不能直接在 msoFileDialogSaveAs 上设置过滤器。但是，如果您将 InitialFileName 设置为“*.ext”，那么它将强制使用该扩展名。过滤器仍会显示“所有文件”，但除非文件具有您提供的扩展名，否则它不会显示文件。
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.
例如，如果您擦除“*.ext”并只写“test”，则生成的文件名将是“test.ext”，因此它实际上会强制使用该扩展名。
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.
它并不完美，但它非常简单，并且无需为那些不太熟悉代码的人调用 API 即可实现所需的结果。
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.
这一次只适用于一个扩展名，例如“*.csv”。如果您需要过滤多个扩展名，例如图像，那么您将不得不求助于使用 API 调用。
回答by simpLE MAn
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
回答by Mike Gardner
http://msdn.microsoft.com/en-us/library/office/aa219834(v=office.11).aspx
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.
使用 filterindex 从默认列表中选择所需的扩展名（启动对话框并将列表倒计时到您的扩展名），或修改 msdn 链接页面中记录的 saveas 过滤器集合。过滤器不能在 filedialog 实例中更改，只能在通过 Application.FileDialog(msoFileDialogSaveAs).Filters 使用 filedialogfilters 对象之前更改。然后它们在实例中可用。
