Exporting multiple worksheets as CSV

[Joe Sheehan]

I'm looking for a way to automatically export multiple worksheets as
CSV. I have some code that will export the Active Sheet. I'm not
looking for fancy renaming - I basically just need to turn each
worksheet in an XLS file into its own CSV file. Has anyone already
done this?

Thanks
Joe

 

[Dave Peterson]

I bet lots of people have a version that did what you asked. Here's one:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim wks As Worksheet

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

MsgBox "done with: " & ActiveWorkbook.Name

End Sub

 

[Mike O]

I'm a complete newbie at this stuff...how would you edit this script so
it only exports a "target" sheet as a CSV?

So instead of multiples, it only exports one.

Thanks,

Mike

 

[Mike O]

I'm a newbie at this...how would you change that script to make it work
if you wanted to "target" the sheet you wanted to export as CSV?

Basically, instead of multiple sheets, just the sheet you want.

Thanks!

Mike

 

[Dave Peterson]

How about:

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim wks As Worksheet

set wks = activeworkbook.worksheets("mysheetnamehere")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:\temp\" & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With


End Sub

 

[Mike O]

Thanks Dave, that worked perfectly!

- Mike

 

[DebalinaRoy]

Hi, I just want to know how will you run this program which you have written