预设“另存为类型". MSApplication中使用Application.FileDialog(msoFileDialogSaveAs)时输入字段

问题描述

我到处搜寻了一种方法.

I searched all over for a way to do this.

我要打开另存为"对话框，以便用户可以选择保存文件的位置.但是，我希望将另存为类型"字段预先设置为逗号分隔值文件(* .csv)"

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)"

问题是"Filter"方法似乎不适用于"msoFileDialogSaveAs".是否可以使用"Application.FileDialog(msoFileDialogSaveAs)"预设文件类型?

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)"?

此刻，如果我保存扩展名为.csv的文件，然后在excel中打开，则会显示"您要打开xxx.csv的文件格式与指定的格式不同".文件扩展名... "消息.该文件可以正常运行.

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

谢谢！

推荐答案

如上所述，他FileDialog帮助状态不支持msoFileDialogSaveAs.

As stated he FileDialog help states msoFileDialogSaveAs is not supported.

当对话框卸载时，您可以在FileName上强制使用CSV扩展名；

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

如果excel不喜欢您的CSV，请检查是否需要引用任何值来转义换行符/(http://stackoverflow.com/questions/566052/can-you-encode-cr-lf-放入csv文件中

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)

而不是这种模式；

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

可以；

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

这篇关于预设“另存为类型". MSApplication中使用Application.FileDialog(msoFileDialogSaveAs)时输入字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！