Saving as Sheet as a Tab Delimited Text file

[Fredriksson via OfficeKB.com]

am trying to write a Macro where I save the Sheet name Template as a Text
file in the current directory with a name format of TemplateMonthYear.txt.

Then I want to save the entire workbook with the name format of
TemplateMonthYear.xls.

I keep getting error messages when it tries to execute the statement

Sheets("Template").SaveAs Filename:= _
"Template" & AcctMth & AcctYear & ".txt", _
FileFormat:=xlText, CreateBackup:=False

Am I using the right statement to save a Sheet in a woorkbook as a tab
delimited text file?

Sub SaveTextFile()
'
' SaveTextFile Macro
'
Dim AcctYear, AcctMth
ActiveWorkbook.Save
Sheets("Template").Select
Range("AcctgPeriod").Select
AcctMth = Str(Month(Range("AcctgPeriod").Value))
AcctYear = Str(Year(Range("AcctgPeriod").Value))
Sheets("Template").Select
Sheets("Template").SaveAs Filename:= _
"Template" & AcctMth & AcctYear & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.SaveAs Filename:= _
"JIBUpload" & AcctMth & AcctYear & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

 

[Jay]

Try changing the term 'xlText' to 'xlTextWindows'. Even though the macro
recorder produces xlText as the FileFormat, xlText is not listed as a valid
filetype...

 

[NickHK]

If you check the Object Browser, you will see that "xlText" is a memebr of
the "XlPivotFieldDataType" enum, with a value of
Const xlText = -4158 (&HFFFFEFC2).

What you actually need is a member of the "XlFileFormat" enum. I would
assume one of these :
Const xlTextWindows = 20 (&H14)
Const xlUnicodeText = 42 (&H2A)

NickHK