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:
- Opening a .csv file in Microsoft Excel (say a data input file for an Actuarial Model);
- Making some modification to this on a Worksheet;
- 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!