5

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.

Improve this question
0

2 Answers 2

Reset to default
7

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...

enter image description here

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...

Improve this answer
1
  • Is it possible to do something like export, instead of Save As? The way your macro is implemented, it changes the working file to the CSV file everytime the shortcut is pressed.
    – neves
    May 4, 2016 at 20:32
4

What you want to do is record a macro that saves your file to CSV:

  1. Go to the Tools menu
  2. Select Macro > Record new macro
  3. Give it a name like 'ToCSV'
  4. You can choose to save it in the current workbook, or globally. In your case, the current workbook should suffice
  5. From the File menu, select Save As... , Select CSV and go through all the annoying dialogs
  6. Once saved, go to Tools > Macros > Stop Recording Macro
  7. To save to CSV, just Select Tools > Macro > Macross... Select your Macro and click run
  8. To streamline the process, you can click the Options... button and assign a shortcut key to your macro

And now you can save to CSV in a click. Good luck!

Improve this answer
1
  • Cool! I have never done much with macros and forget they're available.
    – Sonny
    Jan 24, 2012 at 20:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .