excel vba‎ > ‎

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

posted Feb 1, 2011, 9:37 AM by Roberto Felicini

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

Comments