vba 使用宏将excel工作表以文件名从单元格保存为CSV

Saving excel worksheet to CSV with file name from a cell using a macro

提问by luke

i have a macro that saves a copy of my workbook that works but it saves as a .xlsm and i need it to be saved as a comma delimited .csv file type can any one help me?

我有一个宏可以保存我工作簿的副本，但它保存为 .xlsm，我需要将它保存为逗号分隔的 .csv 文件类型，有人可以帮助我吗？

here is the macro i have now

这是我现在拥有的宏

Sub toCSV()

Dim newWB As Variant
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
With wb1
    .SaveCopyAs ("C:\Users\sales\desktop\") & Range("A2").Text & ".xlsm"
End With

End Sub

子到CSV()

Dim newWB As Variant
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
With wb1
    .SaveCopyAs ("C:\Users\sales\desktop\") & Range("A2").Text & ".xlsm"
End With

End Sub

采纳答案by Mr. Mascaro

Code:

代码：

.SaveAs Filename:=("C:\Users\sales\desktop\") & Range("A2").Text & ".csv", _
FileFormat=:xlCSV

回答by SeanC

This code will take the sheet you want to be saved as a CSV, and copy it to a new workbook before saving

此代码将您要保存为 CSV 的工作表，并在保存之前将其复制到新工作簿

Dim CSVBook As Workbook
Set CSVBook = Workbooks.Add
ThisWorkbook.Sheets("TheCSVSheet").Copy Before:=CSVBook.Sheets(1)
CSVBook.SaveAs Filename:="C:\tmp\test.csv", FileFormat:=xlCSV
CSVBook.Close

This will allow you to save the file as a CSV, but still retain the original macro enabled spreadsheet which you can go back to, and do whatever other processing you need

这将允许您将文件保存为 CSV，但仍保留您可以返回的原始启用宏的电子表格，并执行您需要的任何其他处理

回答by BradyK

Try adding this to your code:

尝试将其添加到您的代码中：

FileFormat:=xlCSVMSDOS

And changing the '.xlsm' to '.csv'

并将“.xlsm”更改为“.csv”

I also highly suggest you look herefor future questions with regard to saving!

我还强烈建议您在此处查看有关储蓄的未来问题！