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

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.

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

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