vba 如何将多个工作表导出为 CSV（不保存当前工作表）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16232245/
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
How to export multiple worksheets to CSV (without saving over the current worksheet)
提问by Tommy O'Dell
I'm trying to export a number of worksheets in my workbook to .csv via some code like this:
我正在尝试通过以下代码将工作簿中的许多工作表导出到 .csv：
Sub Export_To_CSV(exportPath As String)
Dim filePath As String
For Each WS In ThisWorkbook.Worksheets
filePath = exportPath & "(" & WS.Name & ").dat"
WS.SaveAs Filename:=filePath, FileFormat:=xlCSV
Next
End Sub
The problem is that this saves over the current .xlsm file that I have open.
问题是这会保存我打开的当前 .xlsm 文件。
How can I get it to export the .csv without changing the name of the current file?
如何在不更改当前文件名称的情况下导出 .csv？
I thought SaveCopyAswould do the trick, but it only applies to a workbookand not a worksheet.
我认为SaveCopyAs可以解决问题，但它仅适用于 aworkbook而不是 a worksheet。
回答by Kazimierz Jawor
Here comes my idea which could help you...
Add this part of code instead you current for...nextsection:
这是我的想法，它可以帮助你......添加这部分代码而不是你当前的for...next部分：
'...your code here
Dim tmpWS As Worksheet
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
filePath = exportPath & "(" & WS.Name & ").dat"
WS.Copy
Set tmpWS = ActiveSheet
tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV
tmpWS.Parent.Close False
Next
Application.DisplayAlerts = True
'...your code here
Logic of the code? First, it makes a copy of your sheet into temporary workbook, next it saves new sheet as CSV file and finally, it closes temporary workbook. Moreover, I added Application.DisplayAlertsinstructions that your code overwrites .csvfile without asking if file already exists.
代码逻辑？首先，它将工作表的副本复制到临时工作簿中，然后将新工作表另存为 CSV 文件，最后关闭临时工作簿。此外，我添加了Application.DisplayAlerts说明，您的代码会覆盖.csv文件而不询问文件是否已存在。
回答by WillemG
You could also first save the workbook, then save off the worksheets and finally close the workbook without having to save (again):
您也可以先保存工作簿，然后保存工作表，最后关闭工作簿而无需保存（再次）：
' ... your code here
ActiveWorkbook.Save
Application.DisplayAlerts = False
Dim WS As Worksheet
For Each WS In Worksheets
Filepath = "c:\temp\" & WS.Name & ".dat"
WS.SaveAs Filename:=Filepath, FileFormat:=xlCSV, _
CreateBackup:=False
Next
ActiveWorkbook.Close
' ... your code here
