SaveAs without name change

[Pantelis]

I want to write code for a command button to save
the "WorkSheet1" to a .txt (tab oriended) "txtFile1".
I use the following code which works, but the side effect
is the "normal?" name change of the currend .xls file name
to "txtFile1.txt"
-----------------------------------
Private Sub cmdSaveAsTxt_Click()

ActiveWorkbook.SaveAs Filename:=txtFile1, _
FileFormat:=xlText, CreateBackup:=False

End Sub

 

[Tom Ogilvy]

When you do the save as, you are not in the xls file anymore. You are in
the text file (opened as a workbook). Thus the .txt extension. You need
to open the old .xls file and close the text file.

Private Sub cmdSaveAsTxt_Click()
sName = ThisWorkbook.Fullname
ActiveWorkbook.SaveAs Filename:=txtFile1, _
FileFormat:=xlText, CreateBackup:=False
Workbooks.Open sName
ThisWorkbook.Close SaveChanges:=False
End Sub