want to save a copy as text , not save the original as text

[Janis]

I want to save a copy as text not the file itself.
Currently this macro works and saves a text mac file "19". If I change it to
saveCopyAs it doesn't work. It does the same thing if I change it to
another file type
so I don't think it has anything to do with the file type.
I don't want to destroy the workbook but I need to save a copy as text.
Why doesn't saveCopyAs work instead of SaveAs?

Sub saveIndesign()
'Appends date to filename so as to not write over an existing file

' saveIndesign Macro

Const fPath As String = "Mac OS X:"
Dim fName As String
Dim myFileName As String
myFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) &
Format(Now, "yyyymmdd_hhmmss") & ".txt"


fName = fPath & myFileName
Application.DisplayAlerts = False
'do the save


ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=19
Application.DisplayAlerts = True

MsgBox "File Saved to " & fName
End Sub
thanks,

 

[Dave Peterson]

..SaveCopyAs means what it says. It saves a copy of the existing workbook--it
keeps the same fileformat.

One way around this is to copy the worksheet to a new workbook and then save the
..txt file from there.

Sub saveIndesign()

Const fPath As String = "Mac OS X:"
Dim fName As String
Dim myFileName As String
dim Wks as worksheet

myFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & _
Format(Now, "yyyymmdd_hhmmss") & ".txt"

fName = fPath & myFileName

activesheet.copy 'to a new workbook
set wks = activesheet 'that sheet in the new workbook

Application.DisplayAlerts = False
wks.parent.SaveAs Filename:=fName, FileFormat:=19
Application.DisplayAlerts = True

'close that new workbook
wks.parent.close savechanges:=false

MsgBox "File Saved to " & fName
End Sub

(Untested, uncompiled)

And you may want something else besides the activesheet copied.

 

[Helmut Weber]

Hi Janis,

try this:

Sub Macro1()
ActiveWorkbook.SaveAs _
Filename:="c:\test\test.txt", FileFormat:=xlTextMac
' now save again as Excel-File
ActiveWorkbook.SaveAs _
Filename:="c:\test\test.xls", FileFormat:=xlWorkbookNormal
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Janis]

Helmut:
Both these answers worked but this answer was very short and compact.
THANKS!

 

[Bryan Patrick]

Hi Helmut,

I have a similar need (on WindowsXP with Excel2003) except I want the text
copy to be saved automagically whenever the .xls file is saved. I tried
playing around with Workbook_BeforeSave(), but I couldn't get it to work.

Would you mind showing me how to do it correctly?

Kind regards,

Bryan Patrick
Batavia, IL, USA