batch convert worksheets in one workbook into csv files?

[Robgrant]

Is there a way to batch convert many worksheets, contained in one workbook,
into individual csv files?

Thanks

 

[Ron de Bruin]

Hi Robgrant

Try this

You can save every sheet then as a CSV file with a loop
Dave Peterson posted this macro to save each sheet as a CSV

Sub testme()
Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\WINDOWS\TEMP\" & .Name, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks

End Sub

(adjust the path)

 

[Robgrant]

I adjusted the path to D:\CSVDATA
I get stuck on line starting: wks.copy

Can you give me a little more information? I'm rather new at this.

Thanks

 

[Ron de Bruin]

Do you have hidden worksheets in your workbook ?

 

[Robgrant]

Okay, how do I tell?

Do you have hidden worksheets in your workbook ?

 

[Robgrant]

I clicked unhide for the workbook, but still get stuck at the same point in
the macro.

 

[Ron de Bruin]

Hi Rob

Okay, how do I tell?

Format>Sheet...Unhide

Try this instead

Sub testme()
Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = -1 Then
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\Data\" & .Name, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
End If
Next wks
End Sub

 

[Robgrant]

Very pretty, worked like a charm.
Thanks Ron.

Hi Rob

Format>Sheet...Unhide

Try this instead

Sub testme()
Dim wks As Worksheet
Dim newWks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = -1 Then
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\Data\" & .Name, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
End If
Next wks
End Sub

 

[Ron de Bruin]

You are welcome