CODEWITHSUNDEEP
vbaexcel
micstr
micstr

Reputation: 5206

VBA Saving single sheet as CSV (not whole workbook)

I appreciate there are lots of entries like save individual excel sheets as csv and 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

Upvotes: 30

Views: 113771

Answers (4)

Cloud Strife
Cloud Strife

Reputation: 63

Coming to this question several years later, I have found a method that works much better for myself. This is because the worksheet(s) I'm trying to save are large and full of calculations, and they take an inconvenient amount of time to copy to a new sheet.

In order to speed up the process, it saves the current worksheet and then simply reopens it, closing the unwanted .csv window:

Sub SaveThisSheetInParticular()

Dim path As String
path = ThisWorkbook.FullName
Application.DisplayAlerts = False

Worksheets("<Sheet Name>").SaveAs Filename:=ThisWorkbook.path & "\<File Name>", FileFormat:=xlCSV

Application.Workbooks.Open (path)
Application.DisplayAlerts = True
Workbooks("<File Name>.csv").Close

End Sub

Here the Sheet and csv filename are hardcoded, since nobody but the macro creator (me) should be messing with them. However, it could just as easily be changed to store and use the Active Sheet name in order to export the current sheet whenever the macro is called.

Note that you can do this with multiple sheets, you simply have to use the last filename in the close statement:

Worksheets("<Sheet 1>").SaveAs Filename:=ThisWorkbook.path & "\<File 1>", FileFormat:=xlCSV
Worksheets("<Sheet 2>").SaveAs Filename:=ThisWorkbook.path & "\<File 2>", FileFormat:=xlCSV

[...]

Workbooks("<File 2>.csv").Close

Upvotes: 3

Steve B
Steve B

Reputation: 79

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

Upvotes: 7

Vulthil
Vulthil

Reputation: 768

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.

Upvotes: 38

PaulG
PaulG

Reputation: 1199

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.

Don't forget to put it back to true though.

Upvotes: 3

Related Questions