如何使用VBA将Excel文件另存为CSV,新文件不会向我显示错误消息?

时间:2021-06-12 02:23:26

I know there are similar questions (and answers) on Stack Overflow but I'm specifically trying to prompt the end user to save the file and rename it as appropriate, not have the file automatically created.

我知道Stack Overflow上有类似的问题(和答案),但我特意试图提示最终用户保存文件并根据需要重命名,而不是自动创建文件。

I'm trying to create a macro that will automatically export a worksheet in my workbook and save the file for non-technical users, as a CSV. The macro actually works fine, but each file it creates gives me the error message "The file format and extension of 'NAME OF FILE.csv' don't match. The file could be corrupted or unsafe. unless you trust its source, don't open it. Do you want to open it anyway?" How can I have this new file open up without this warning message? Here's my code:

我正在尝试创建一个宏,它将自动导出工作簿中的工作表并将文件保存为非技术用户,如CSV。宏实际上工作正常,但它创建的每个文件都给出了错误消息“'NAME OF FILE.csv'的文件格式和扩展名不匹配。文件可能已损坏或不安全。除非您信任其源,否则打开它。你还想打开它吗?“如何在没有此警告消息的情况下打开此新文件?这是我的代码:

Sub copy_translated_file()

Dim DstFile As String 'Destination File Name
Dim wb As Workbook
Dim InitFile As String

InitFile = "BE SURE TO CHANGE THE NAME OF THIS FILE TO WHAT YOU WANT.csv"
    Sheets("Translated").Select
    Sheets("Translated").Copy

    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     Application.CutCopyMode = False

Set wb = ActiveWorkbook

ActiveSheet.Buttons.Delete 'Get rid of the macro button

    Columns("N:N").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft
    Range("F1").Select

DstFile = Application.GetSaveAsFilename(InitialFileName:=InitFile, _
                                        fileFilter:="CSV (*.csv), *.csv", _
                                        FilterIndex:=1, _
                                        Title:="Save As")
        If DstFile = "False" Then
            MsgBox "Actions Canceled. File not saved."
            wb.Close savechanges:=False 'Close File
            Exit Sub
        Else
        wb.SaveAs DstFile 'Save file
        wb.Close False 'Close file
        MsgBox ("Translated file successfully saved in specified location.")
        End If

End Sub

1 个解决方案

#1


0  

That is a setting in Excel itself, going to File -> Options -> Trust Center

这是Excel本身的一个设置,转到文件 - >选项 - >信任中心

Also, ensure your flag is set for FileFormat:=xlCSV

另外,确保为FileFormat:= xlCSV设置了标志

#1


0  

That is a setting in Excel itself, going to File -> Options -> Trust Center

这是Excel本身的一个设置,转到文件 - >选项 - >信任中心

Also, ensure your flag is set for FileFormat:=xlCSV

另外,确保为FileFormat:= xlCSV设置了标志