Save Excel WorkSheet as CSV

In this short post we are going to look at a technique to save an Excel WorkSheet to .csv. The goal is to save the worksheet to a new .csv file while keeping the existing WorkBook and WorkSheet open.

Problem Outline

Something I find myself doing a lot these days while working on model developments is the following:

1. Opening a .csv file in Microsoft Excel (say a data input file for an Actuarial Model);
2. Making some modification to this on a Worksheet;
3. Then wanting to save the modified version as a new .csv file;

The final step of saving the modified version of the file as a new .csv file starts to become a tedious manual task after awhile. It’s fine when when you only have to do it once or twice but when you are doing it on a regular basis you want some way to automate it.

This can be done with a simple VBA macro but it turns out it’s not as simple as we might like to do this while keeping the existing file open! The remainder of this short article provides details on what we need to do to achieve our goal and provides VBA code to achieve it.

Steps to Save Excel WorkSheet as CSV

At a high level, we have to take the following steps:

• Copy the worksheet to a new Workbook;
• Save the new workbook as a .csv file (while the relevant WorkSheet is active);
• Appropriately close down the (temporary) workbook;
• Clean up any resources;

VBA Code We Can Use to Automate

Below is a short subroutine which I keep within my own personal macro workbook and which can be used to save the active worksheet as a .csv file.

Option Explicit

Public Sub SaveWorkSheetAsCsv()
    Dim vResult As Variant
    vResult = Application.GetSaveAsFilename( _
                        InitialFileName:=Application.ActiveWorkbook.path & "\" & _
    										Application.ActiveSheet.Name & ".csv", _
                        FileFilter:="CSV (Comma delimited) (*.csv), *.csv", _
                        Title:="Choose Save Location")
    
    ' user cancelled so don't do anything
    If vResult = False Then GoTo CleanUp
    
    ' Steps are:
    ' 1. Add a new workbook to the application;
    ' 2. Copy the worksheet into that workbook;
    ' 3. Save the csv file;
    ' 4. Then close that new workbook;
    
    Dim csvWks As Worksheet
    Set csvWks = Application.ActiveSheet
    
    Dim newWbk As Workbook
    Set newWbk = Workbooks.Add(1)
    
    csvWks.Copy After:=newWbk.Sheets(newWbk.Sheets.Count)
    newWbk.Sheets(newWbk.Sheets.Count).SaveAs CStr(vResult), xlCSV
    ActiveWorkbook.Close SaveChanges:=False

CleanUp:
    On Error Resume Next
        If Not csvWks Is Nothing Then Set csvWks = Nothing
        If Not newWbk Is Nothing Then Set newWbk = Nothing
    On Error GoTo 0
    
End Sub

Conclusions

In this brief post we covered how to easily automate saving a given WorkSheet to a new .csv file without closing down the existing WorkBook.

If you liked this post please give it a Like below and share on social media. See you next time!