Export to CSV From Excel

I frequently use Excel to prepare data to load into a table. I create a CSV file from it and use that file for data loading. I generally find myself creating the CSV file multiple times.

My current workflow:

1. Edit spreadsheet


2. File -> Save As -> *.csv


3. Dismiss the "...selected file type does not support workbooks that contain multiple sheets..." dialog


4. Dismiss the "...may contain features that are not compatible with CSV..." dialog


5. Close the CSV file and click "Don't Save" on the dialog warning about saving changes made


6. Re-open the original spreadsheet file


7. Repeat the process until done

What I would like to be able to do:

1. Edit spreadsheet


2. File -> Export -> *.csv


3. Repeat the process until done

I can't find a way to accomplish my desired workflow.

Answer

Traveling Tech Guy's answer is certainly correct as far as it goes. A few points though - if you save the Macro in the current workbook, it won't be available when you start another workbook. You probably want to save it in the Personal Workbook - this is an Excel file that loads automatically every time you start Excel; it's a worksheet but it's hidden. That makes the macros saved in it available to other workbooks in the same Excel session.

Another issue is that the macro will probably record the folder and file name you are saving to. If you don't mind writing to the same file every time (not just in this session, but in all future sessions) that's cool, but if you want to be able to save the current worksheet to CSV without writing over the CSV files you created in previous sessions, you need to edit the macro and remove the Filename parameter. The macro without file name looks like this after you edit it...

Once this is done you can save any Excel file in CSV format by just pressing the assigned shortcut, Ctrl-x in this example. It will save to the same folder and name as the original XLS file but with the CSV extension - you don't have to choose a path or filename and it won't write over other CSV files from other worksheets. No warning messages, not even a confirmation dialog - just blink and done.

One last detail - when you go to edit a Macro that is saved in the Personal workbook, Excel says you can't because it's "hidden". This refers to the Hide/Unhide commands on the toolbar under the View menu - it doesn't mean you have to find the file in Explorer and remove the "hidden" file attribute. That wasted a few minutes of my time!

Good luck...