Saving a woorkbook as a TXT file keeping its current name

[Chichifo]

I'm really new to macros and I want to use a macro for several exce
files. I need to save them at the end as a Text (Tab delimited)(*.txt
however I want to keep the original file name. How can I make Book1.tx
a variable so it will take the woorkbook name that the macro i
processing.

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Book1.txt", FileFormat:= _
xlText, CreateBackup:=False
THX

 

[Ron de Bruin]

Try this to save the activesheet as a txt file

Sub test()
Dim wb As Workbook
Set wb = ActiveWorkbook
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\" & Left(wb.Name, Len(wb.Name) - 4) & ".txt", FileFormat:= _
xlText, CreateBackup:=False
ActiveWorkbook.Close False
End Sub

More info you can find here
http://www.cpearson.com/excel/imptext.htm

 

[Nick Hodge]

This should work for you

Dim wb As Workbook
Dim wbName As String
Set wb = ActiveWorkbook
wbName = Left(wb.Name, Len(wb.Name) - 4)
wb.SaveAs Filename:= _
"C:\Documents and Settings\" & wbName & ".txt", FileFormat:= _
xlText, CreateBackup:=False

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Mike D.]

'Make a variable named Fname

Fname = ActiveWorkbook.Name

Use the InStr keyword to trim out the ".xls" (I can't
remember the whole syntax, but something like: InStr
(Fname, ".xls", <can't remember the rest, but when you
start typing the syntax should pop up>)

'after that add the ".txt"
Fname = Fname & ".txt"

'next save it
ActiveWorkbook.SaveAs Filename:= _

 

[Nick Hodge]

Ron

I didn't copy your code, honest, scary how we all use the same variable
acronyms

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

This should work for you

Dim wb As Workbook
Dim wbName As String
Set wb = ActiveWorkbook
wbName = Left(wb.Name, Len(wb.Name) - 4)
wb.SaveAs Filename:= _
"C:\Documents and Settings\" & wbName & ".txt", FileFormat:= _
xlText, CreateBackup:=False

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Chichifo]

Thank you guys, it is working now