VBA将单个工作表另存为CSV(不是整个工作簿)

问题描述

我很感谢有很多条目，例如将单个Excel工作表另存为csv 和将每张工作表导出到单独的csv文件-但是我想要在工作簿中保存单个工作表.

我的xlsm文件中的代码有一个参数和数据表.我使用粘贴的值创建了数据的工作表副本，然后将其另存为csv.目前，我的整个工作簿都更改了名称并变成了csv.

如何在Excel工作簿中将单个工作表另存为csv"?

是否有Worksheet.SaveAs还是必须将数据表移动到另一个工作簿并以这种方式保存?

代码示例

' [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?

使用Windows 10和Office 365

解决方案

此代码对我来说很好.

Sub test()

Application.DisplayAlerts = False

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname, FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

Application.DisplayAlerts = True

End Sub

它正在复制整个strSourceSheet表，这将打开一个新工作簿，然后我们可以将其另存为.csv文件，然后关闭新保存的.csv文件，而不会弄乱原始文件中的文件名.

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 worksheet in a workbook.

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.

How do I "save as csv" a single sheet in an Excel workbook?

Is there a Worksheet.SaveAs or do I have to move my data sheet to another workbook and save it that way?

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

解决方案

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.

这篇关于VBA将单个工作表另存为CSV(不是整个工作簿)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！