Save one sheet as .csv with macro
 

I have an Excel file with more than one worksheet. With the Excel file open,
I want to save one of the sheets as a csv file (but name it with a .txt
extension) and leave the original file open (still as an Excel file) to
continue working with it. I have tried using the SaveAs function and I end
up with both the sheet and the original file with a .txt extension--not what
I want. I have also figured out how to create both files IF I close both
files--also not what I want.

I have searched online and have not come up with any help for this scenario.
I would really appreciate advice from someone who can get this working.

Thank you,
Judy

Save one sheet as .csv with macro
 

One arduous solution is to write VBA code that opens a text file, reads the
sheet cell by cell and writes them to the file and closes the file.

A simpler solution is to use the Jet 4.0 provider to read the sheet in its
entirety into an ADO recordset object and then to write this object , using
its GetString() method to a TXT file. This method allows full control: you
can write a subset of any sheet to a txt file also.

Save one sheet as .csv with macro
 

Judy Ward wrote:
I have an Excel file with more than one worksheet. With the Excel
file open, I want to save one of the sheets as a csv file (but name
it with a .txt extension) and leave the original file open (still as
an Excel file) to continue working with it. I have tried using the
SaveAs function and I end up with both the sheet and the original
file with a .txt extension--not what I want. I have also figured out
how to create both files IF I close both files--also not what I want.

I have searched online and have not come up with any help for this
scenario. I would really appreciate advice from someone who can get
this working.

Thank you,
Judy

Get a copy of the free ASAP Utilities addin. It allows one to expost a
selection to CSV

Save one sheet as .csv with macro
 

You can copy that sheet to a new workbook and save it from there.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet

set wks = worksheets("whateveroneyouwanthere")

wks.Copy 'to a new workbook
Set newWks = ActiveSheet

With newWks
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\TEMP\" & .Name & ".txt", _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With

End Sub

Judy Ward wrote:

I have an Excel file with more than one worksheet. With the Excel file open,
I want to save one of the sheets as a csv file (but name it with a .txt
extension) and leave the original file open (still as an Excel file) to
continue working with it. I have tried using the SaveAs function and I end
up with both the sheet and the original file with a .txt extension--not what
I want. I have also figured out how to create both files IF I close both
files--also not what I want.

I have searched online and have not come up with any help for this scenario.
I would really appreciate advice from someone who can get this working.

Thank you,
Judy

--

Dave Peterson