How do I copy a worksheet from a workbook as csv file
 

I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Husker

In order to record a macro one must go to ToolsMacroRecord New Macro.

And when done the macro usually must be edited somewhat.

Sub Macro2()
Dim w As Worksheet
Set w = Sheets("CSV Data")
Application.DisplayAlerts = False
w.Copy
ActiveWorkbook.SaveAs Filename:="E:\GordStuff\" & w.Name, _
FileFormat:=xlCSV
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub


Gord Dibben Excel MVP

On Thu, 24 Mar 2005 12:55:05 -0800, "Husker87"
wrote:

I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Short follow up... btw this worked great. I changed the C: to A: so I could
save it to a disk. Here is my follow-up... can I change your code that
names the file to include the content of a cell on the CSV Data sheet? For
example the user has to enter a unique number into a cell "E1" on the CSV
Data worksheet. I would like the name of the CSV file to start with that
number in "E1" then followed by the date like you wrote it. Is that
possible?

"Ron de Bruin" wrote:

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Hi

First of all never save to a floppy this way.
Always save to C:\ and copy manual to A


Try this

Sub Sheet_CSV_File_2()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
strdate & ".csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
Short follow up... btw this worked great. I changed the C: to A: so I could
save it to a disk. Here is my follow-up... can I change your code that
names the file to include the content of a cell on the CSV Data sheet? For
example the user has to enter a unique number into a cell "E1" on the CSV
Data worksheet. I would like the name of the CSV file to start with that
number in "E1" then followed by the date like you wrote it. Is that
possible?

"Ron de Bruin" wrote:

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Worked GREAT. Thanks again. btw, why would you not want to save it to a
floppy but the c: drive first?

"Ron de Bruin" wrote:

Hi

First of all never save to a floppy this way.
Always save to C:\ and copy manual to A


Try this

Sub Sheet_CSV_File_2()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
strdate & ".csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
Short follow up... btw this worked great. I changed the C: to A: so I could
save it to a disk. Here is my follow-up... can I change your code that
names the file to include the content of a cell on the CSV Data sheet? For
example the user has to enter a unique number into a cell "E1" on the CSV
Data worksheet. I would like the name of the CSV file to start with that
number in "E1" then followed by the date like you wrote it. Is that
possible?

"Ron de Bruin" wrote:

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?

Good morning

Worked GREAT. Thanks again. btw, why would you not want to save it to a
floppy but the c: drive first?

The chance of file Corruption is big




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
Worked GREAT. Thanks again. btw, why would you not want to save it to a
floppy but the c: drive first?

"Ron de Bruin" wrote:

Hi

First of all never save to a floppy this way.
Always save to C:\ and copy manual to A


Try this

Sub Sheet_CSV_File_2()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "C:\" & wb.Sheets(1).Range("E1") & " " & _
strdate & ".csv", FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
Short follow up... btw this worked great. I changed the C: to A: so I could
save it to a disk. Here is my follow-up... can I change your code that
names the file to include the content of a cell on the CSV Data sheet? For
example the user has to enter a unique number into a cell "E1" on the CSV
Data worksheet. I would like the name of the CSV file to start with that
number in "E1" then followed by the date like you wrote it. Is that
possible?

"Ron de Bruin" wrote:

Try this one to save the file in C:\

Sub Sheet_CSV_File()
Dim wb As Workbook
Dim strdate As String
Dim Fname As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")

Fname = "C:\Part of " & ThisWorkbook.Name _
& " " & strdate & ".csv"

Application.ScreenUpdating = False
Sheets("CSV Data").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs Fname, FileFormat:=xlCSV
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Husker87" wrote in message ...
I have a workbook with several worksheets. I want to record a macro that
will save one tab, "CSV Data" as its own csv file to another location. Any
ideas?