Sunday, March 31, 2013

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

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 Tab Delimited 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 ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog