how to export ALL worksheets in an Excel spreadsheet to individual CSV files

[Guest]

hi all,
i have an excel spreadsheet with many worksheets. i need to export each
worksheet to a separate CSV file, ideally the filname would be the
worksheet's name.

is this possible? using the 'save as' option on the File menu, i have to
save each worksheet manually.


thanks,


callum

 

[steam3801]

hi all,
i have an excel spreadsheet with many worksheets. i need to export each
worksheet to a separate CSV file, ideally the filname would be the
worksheet's name.

is this possible? using the 'save as' option on the File menu, i have to
save each worksheet manually.


thanks,


callum

You can select all/each worksheet by holding down the CTRL key and
selecting individual/all sheets name tabs (at the bottom)

BUT

when you try to Save As ... and csv file, error msg appears saying
this file format is not supported for multiple selection of wkshts

So, looks like you can't if you want to use csv format.

You know it makes sense.
steam3801

 

[Frank Kabel]

Hi
you can try the following macro

Sub Save_All_CSV()
Dim i As Integer
Dim Datei_name As String
Dim Pfad As String

Datei_name = ""
Pfad = "D:\temp\"

For i = 1 To Sheets.Count
Sheets(i).Select
Datei_name = Pfad & Sheets(i).Name & ".csv"
ActiveWorkbook.SaveAs Filename:=Datei_name, FileFormat:=xlCSV,
CreateBackup:=False
Next i
End Sub

Note: no error checking included, etc.

Frank

 

[Guest]

apart from the line

CreateBackup:=False

that worked really well. thanks very much for your help!



callum

 

[Frank Kabel]

Hi sorry for that,

i hate word wrapping. This line belongs to the line before
Frank