copy active shhe in a new worksheet and save it in *.TXT delimited

Post date: Feb 1, 2011 5:37:06 PM

Sub export_TXT()

Application.DisplayAlerts = False

Name = ActiveSheet.Name

company = Range("c2").Value

Condition = Range("a2").Value

Table = Range("b2").Value

ActiveSheet.Name = "pricing_" & Condition

Name = ActiveSheet.Name

ActiveSheet.Copy

' Save file name and path into a variable

template_file = ActiveWorkbook.FullName

' Default directory would be c:\temp. Users however will have the ability to change where to save the file if need be.

' Notice that i'm only allowing the save as option to be of .txt format.

' I'm also attaching the current date to the file name.

fileSaveName = Application.GetSaveAsFilename( _

InitialFileName:="C:\" & Name & "_" & Table & "_" & company & "_" & VBA.Strings.Format(Now, "yyyymmdd_hhnnss") + ".txt", _

fileFilter:="Text Files (*.txt), *.txt")

If fileSaveName = False Then

Exit Sub

End If

' Save file as .txt TAB delimited

ActiveWorkbook.SaveAs Filename:= _

fileSaveName, FileFormat:=xlText, _

CreateBackup:=False

file_name_saved = ActiveWorkbook.FullName

MsgBox "Your file has been successfully created at: " & vbCr & vbCr & file_name_saved

ActiveWorkbook.Close False

ActiveWorkbook.Save

Application.DisplayAlerts = True

End Sub