vba VBA将单个工作表另存为CSV（不是整个工作簿）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34155718/
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
VBA Saving single sheet as CSV (not whole workbook)
提问by micstr
I appreciate there are lots of entries like save individual excel sheets as csvand Export each sheet to a separate csv file- But I want to save a single worksheetin a workbook.
我很欣赏有很多条目，比如将单个 Excel 工作表保存为 csv并将每个工作表导出到一个单独的 csv 文件- 但我想在工作簿中保存一个工作表。
My code in my xlsm file has a params and data sheet. I create a worksheet copy of the data with pasted values and then want to save it as csv. Currently my whole workbook changes name and becomes a csv.
我的 xlsm 文件中的代码有一个参数和数据表。我使用粘贴的值创建数据的工作表副本，然后想将其保存为 csv。目前，我的整个工作簿都更改了名称并变成了 csv。
How do I "save as csv" a single sheet in an Excel workbook?
如何将 Excel 工作簿中的单个工作表“另存为 csv”？
Is there a Worksheet.SaveAsor do I have to move my data sheet to another workbook and save it that way?
是否有Worksheet.SaveAs或我必须将我的数据表移动到另一个工作簿并以这种方式保存？
CODE SAMPLE
代码示例
' [Sample so some DIMs and parameters passed in left out]
Dim s1 as Worksheet
Dim s2 as Worksheet
Set s1 = ThisWorkbook.Sheets(strSourceSheet)
' copy across
s1.Range(s1.Cells(1, 1), s1.Cells(lastrow, lastcol)).Copy
' Create new empty worksheet for holding values
Set s2 = Worksheets.Add
s2.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' save sheet
s2.Activate
strFullname = strPath & strFilename
' >>> BIT THAT NEEDS FIXIN'
s2.SaveAs Filename:=strFullname, _
FileFormat:=xlCSV, CreateBackup:=True
' Can I do Worksheets.SaveAs?
Using Windows 10 and Office 365
使用 Windows 10 和 Office 365
回答by Vulthil
This code works fine for me.
这段代码对我来说很好用。
Sub test()
Application.DisplayAlerts = False
ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
It's making a copy of the entire strSourceSheet sheet, which opens a new workbook, which we can then save as a .csv file, then it closes the newly saved .csv file, not messing up file name on your original file.
它正在制作整个 strSourceSheet 工作表的副本，这会打开一个新工作簿，然后我们可以将其另存为 .csv 文件，然后关闭新保存的 .csv 文件，而不是弄乱原始文件上的文件名。
回答by PaulG
You just need to save the workbook as a CSV file.
Excel will pop up a dialog warning that you are saving to a single sheet, but you can suppress the warning with Application.DisplayAlerts = False.
您只需要将工作簿另存为 CSV 文件。Excel 将弹出一个对话框警告您正在保存到单个工作表，但您可以使用以下命令取消警告Application.DisplayAlerts = False.
Don't forget to put it back to true though.
但不要忘记将其恢复为true。
回答by Steve B
This is fairly generic
这是相当通用的
Sub WriteCSVs()
Dim mySheet As Worksheet
Dim myPath As String
'Application.DisplayAlerts = False
For Each mySheet In ActiveWorkbook.Worksheets
myPath = "\myserver\myfolder\"
ActiveWorkbook.Sheets(mySheet.Index).Copy
ActiveWorkbook.SaveAs Filename:=myPath & mySheet.Name, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close
Next mySheet
'Application.DisplayAlerts = True
End Sub
