Export each worksheet to csv

  • Thread starter Thread starter Trickster
  • Start date Start date
T

Trickster

Hi. I've been trying to automate exporting each worksheet in a workbook as a
separate CSV file. I've concocted my own macro by using the recorder and
adapting several other macros I came across, but it doesn't work (I have to
admit I'm not really familiar with VBA):

Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As Sheet

ChDir "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In Sheets
ActiveSheet
ActiveSheet.SaveAs Filename:="C:\Documents and Settings\trickster\My
Documents\Voltec\", FileFormat:=xlCSV, CreateBackup:=False & Fname

Sh.Save
Next Sh
Application.StatusBar = "All Sheets Saved."

End Sub

When I run this, I get Compile error:

User-defined type not defined.

Perhaps someone could help me get it working?
 
Trickster said:
Hi. I've been trying to automate exporting each worksheet in a workbook as a
separate CSV file. I've concocted my own macro by using the recorder and
adapting several other macros I came across, but it doesn't work (I have to
admit I'm not really familiar with VBA):

Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As Sheet

ChDir "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In Sheets
ActiveSheet
ActiveSheet.SaveAs Filename:="C:\Documents and Settings\trickster\My
Documents\Voltec\", FileFormat:=xlCSV, CreateBackup:=False & Fname

Sh.Save
Next Sh
Application.StatusBar = "All Sheets Saved."

End Sub

When I run this, I get Compile error:

User-defined type not defined.

Perhaps someone could help me get it working?
Click to expand...

Well never mind, I found something which pretty much does what I need:

From: Tom Ogilvy
Subject: Re: macro to export each sheet to txt
View: Complete Thread (2 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 2003-06-19 10:03:19 PST


Dim sh as WorkSheet
for each sh in ThisWorkbook.Worksheets
sh.copy
ActiveWorkbook.SaveAs Thisworkbook.Path & "\" & sh.name & ".csv", _
Fileformat:=xlCsv
ActiveWorkbook.Close SaveChanges:=False
Next
 
Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As worksheet
Dim sPath as String
Dim fName as String
sPath = "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In workSheets
sh.copy ' creates new workbook
fname = sh.name
ActiveWorkbook.SaveAs Filename:= _
sPath & fname _
& ".csv", FileFormat:=xlCSV, CreateBackup:=False
Activeworkbook.Close SaveChanges:=False

Next Sh
MsgBox "All Sheets Saved."

End Sub

You need to copy each sheet to a new workbook, which this does.
 
Thanks.

Tom Ogilvy said:
Public Sub ExportAsCSV()
'
' ExportAsCSV Macro
'
'
Dim Sh As worksheet
Dim sPath as String
Dim fName as String
sPath = "C:\Documents and Settings\trickster\My Documents\Voltec\"

For Each Sh In workSheets
sh.copy ' creates new workbook
fname = sh.name
ActiveWorkbook.SaveAs Filename:= _
sPath & fname _
& ".csv", FileFormat:=xlCSV, CreateBackup:=False
Activeworkbook.Close SaveChanges:=False

Next Sh
MsgBox "All Sheets Saved."

End Sub

You need to copy each sheet to a new workbook, which this does.

--
Regards,
Tom Ogilvy


as
Click to expand...
 
Back
Top