9

I need to parse Excel work sheets. Now I save each individual work sheet as .csv and it works great. I use OpenCSV to parse the files etc. but to create those .csv files are a pain.

What would be the easiest and quickest way to save individual work sheets as .csv in Excel? I am assuming some kind of VBA macro would do the job, but since I am not a VBA programmer I have no idea how to do it. Maybe I can just record a macro somehow?

Marthinus
  • 763
  • 2
  • 5
  • 14

2 Answers2

19

Very roughly,

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.SaveAs "C:\docs\" & ws.Name & ".csv", xlCSV
Next

This does not include any error coding nor does it make allowances for sheet names that will lead to illegal file names. It all depends on how robust you need the whole thing to be.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • This work brilliant, I even went fancy and did a 'ActiveWorkbook.SaveAs FileName:=FileFolder & (FileName + " " + newName + "-" + Lpad(CStr(i), "0", 2)) & ".csv", FileFormat:=xlCSV' – Marthinus Sep 08 '11 at 12:55
  • @Marthinus Can you explain where you are getting `FileFolder` and `FileName`? – FarFigNewton Apr 10 '12 at 15:34
  • I expect `FileFolder` and FileName` are taken using `ThisWorkbook.FullName`. – Pezzzz Jun 03 '13 at 13:48
  • What about format of cells? If a cell of type date is formatted as DD/MM then on saving as CSV results as DD/MM and the Year part is omitted. Can you please advice? – bjan Jun 26 '13 at 09:12
3

As a first answer, here is the code used to save a file to CSV:

ActiveWorkbook.SaveAs "C:\Marthinus.csv", fileformat:=6

More info about SaveAs

JMax
  • 26,109
  • 12
  • 69
  • 88