How to make more than one csv file from xls file?

Question

• I have Excel file with 12 sheets.
• When trying to save the file as CSV file (Save As ...), I had to do that 12 times manually (for every sheet separately).

Is there a way not to do that manually or to do that manually in one step?

Answer 1

I have provided a vba version of my vbs code from Export each sheet to a separate csv file

It saves individual sheets to the same path as the host workbook (ActiveWorkbook) with the name of each sheet tab being used from the host workbook for the filename(s).

Sub SaveAsCSV()
Dim objWB As Workbook
Dim objws As Worksheet

Set objWB = ActiveWorkbook

If Len(objWB.Path) = 0 Then
MsgBox ActiveWorkbook.Name & " is not saved. Please save file then re-run this code", vbCritical
Exit Sub
End If

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

For Each objws In objWB.Sheets
  objws.Copy
  ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", xlCSV
  ActiveWorkbook.Close False
Next

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub