Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I export data from one worksheet to another? | Excel Discussion (Misc queries) | |||
Export Excel WorkSheet | Excel Discussion (Misc queries) | |||
Needed: worksheet export help | Excel Discussion (Misc queries) | |||
Export Worksheet to Access | Excel Programming | |||
Export Worksheet to Access | Excel Programming |