简单的 VBA/Macro 需要使用活动工作表的内容创建一个新的文本文件而不更改文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2321875/
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
Simple VBA/Macro need to create a new text file with the contents of active sheet without changing filename
提问by John Smithe
I need to export data in a sheet to a text file without changing the file name (i.e. not doing "save as". Also it would be great if the file name could look at the previous like file name in the folder and increase by 1 digit (i.e. :file_1.txt, file_2.txt, etc.)...
我需要将工作表中的数据导出到文本文件而不更改文件名（即不执行“另存为”。如果文件名可以查看文件夹中以前的类似文件名并增加1，那就太好了数字（即：file_1.txt、file_2.txt 等）...
Thanks!!
谢谢！！
回答by Treb
If you want to avoid the current name of your excel file being changed, just save the current worksheet, not the whole workbook (the VBA equivalent of the SaveAsfunction is ActiveWorkbook.SaveAS, to save just the current sheet use ActiveSheet.SaveAS).
如果要避免更改 excel 文件的当前名称，只需保存当前工作表，而不是整个工作簿（与SaveAs函数等效的 VBA 是 ，仅保存当前工作ActiveWorkbook.SaveAS表 use ActiveSheet.SaveAS）。
You can use the following macro:
您可以使用以下宏：
Sub Macro1()
Application.DisplayAlerts = False
ActiveSheet.SaveAs Filename:="NewFile.txt", FileFormat:=xlTextWindows
Application.DisplayAlerts = True
End Sub
Toggling the DisplayAlertsproperty avoids a message box that is displayed if the given file already exists.
切换该DisplayAlerts属性可避免在给定文件已存在时显示消息框。
If want to save more than one sheet, you need to iterate through the Sheetscollection of the ActiveWorkbookobject and save each sheet to a separate file.
如果要保存多个工作表，则需要遍历对象的Sheets集合ActiveWorkbook并将每个工作表保存到单独的文件中。
回答by Fionnuala
You can get a new file name as illustrated below, it includes a date. If you would like to add some details on what you want to export, you may get a fuller answer.
您可以获得如下所示的新文件名，其中包含日期。如果您想添加一些有关要导出的内容的详细信息，您可能会得到更完整的答案。
Function NewFileName(ExportPath)
Dim fs As Object '' or As FileSytemObject if a reference to
'' Windows Script Host is added, in which case
'' the late binding can be removed.
Dim a As Boolean
Dim i As Integer
Dim NewFileTemp As string
Set fs = CreateObject("Scripting.FileSystemObject")
NewFileTemp = "CSV" & Format(Date(),"yyyymmdd") & ".csv"
a = fs.FileExists(ExportPath & NewFileTemp)
i = 1
Do While a
NewFileTemp = "CSV" & Format(Date(),"yyyymmdd") & "_" & i & ".csv"
a = fs.FileExists(ExportPath & NewFileTemp)
i = i + 1
If i > 9 Then
'' Nine seems enough times per day to be
'' exporting a table
NewFileTemp = ""
MsgBox "Too many attempts"
Exit Do
End If
Loop
NewFileName = NewFileTemp
End Function
