Sunday, 31 March 2013

How to Export or Save Each Sheet as Tab Delimited Text File

Excel VBA Macro To Export or Save Each Sheet as Tab Delimited Text File With Back Up
'This Macro Creates a Folder With Workbook Name & Date Stamp and Saves Each Worksheet as a TabDelimited Text File and Keep a Copy of Excel Sheet as Back up In Target Folder
Sub Export_Each_Sheet_As_TabDelimited_TextFile()
Dim WS As Worksheet
Dim MyStr1 As String
Dim MyStr2 As String
Dim MyPath As String
Dim SavePath As String
Dim MyDate
Dim MyTime

MyDate = Date    ' MyDate Returns the current system date.

MyTime = Time    ' Returns current system time.

Application.DisplayAlerts = False

Application.ScreenUpdating = False
On Error Resume Next

MyStr1 = Format(MyDate, "DD-MM-YYYY")

'Use MyStr2 If You Require Time Stamp In File Name
'MyStr2 = Format(MyTime, "HH.MM.SS")
MyPath = "C:\Documents and Settings\Administrator\My Documents\"
MkDir MyPath & MyStr1 & "_" & ThisWorkbook.Name
SavePath = MyPath & MyStr1 & "_" & ThisWorkbook.Name & "\"

For Each WS In ThisWorkbook.Sheets

WS.Activate
ActiveSheet.Copy
'Exporting Sheet as Tab Delimited Text File To Target Path
ActiveSheet.SaveAs Filename:=SavePath & WS.Name, FileFormat:=xlTextWindows
'Saving a Backup Copy of a Sheet in Target Path
ActiveSheet.SaveAs Filename:=SavePath & WS.Name, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close Savechanges:=True
Next WS
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ThisWorkbook.Activate
'ThisWorkbook.Close Savechanges:=True
'Application.Quit
End Sub

Thanks.,
TAMATAM
  Reporting Analyst

2 comments:

  1. Why 'activesheet.copy' if it's not being pasted anywhere?

    ReplyDelete
    Replies
    1. Hi Steven ,
      'Activesheet.copy' will paste the Activesheet into a new workbook.
      It works as "Move/Copy > Create Copy > New Workbook.

      For better under standing , record the Macro then try to Move/Copy a sheet to New Workbook , tick 'Create Copy' option while moving.
      Now the Generated code will be same shows as "Activesheet.copy".

      Thanks for Following my Blog.
      TAMATAM

      Delete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts