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

Question

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?

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

Solution 1

Code:

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

Solution 2

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

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

Comments

[luke]

this works but i need to keep the current workbook open this leaves the original workbook and and goes to the saved one i would like if passable to have a macro that saves a separate copy of the workbook and keeps the original open

[Mr. Mascaro]

.SaveCopyAs does not support changing file type, so you can simply add a Worksheet.Copy with no parameters before the .SaveAs and change the .SaveAs to ActiveWorkbook.SaveAs Filename:=("C:\Users\sales\desktop\") & wb1.Range("A2").Text & ".csv", _ FileFormat=:xlCSV And then an ActiveWorkbook.Close after that.