Save Excel WorkSheet as CSV

Save Excel WorkSheet as CSV

Excel WorkSheet to 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.

mSaveAsCSV.bas

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!