Export each worksheet to csv
 

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?

Export each worksheet to csv
 

"Trickster" wrote in message
...
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?



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

From: Tom Ogilvy
Subject: 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

Export each worksheet to csv
 

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


Trickster wrote in message
...
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?

Export each worksheet to csv
 

Thanks.

"Tom Ogilvy" wrote in message
...
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


Trickster wrote in message
...
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?