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

'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.Close Savechanges:=True
End Sub

  Reporting Analyst


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

    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.


Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts