Prev: using cells property
Next: Environ("Username") with Windows7 64bit
From: D_Rennie on 15 Feb 2010 02:36

The way i posted the code, The NG feed wont support php tags from the
looks of things.

Option Explicit

Sub SaveSheetAsTXT()
'Save Each Sheet As Seperate File
Dim WS As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
Sheets(WS.Name).Select
Sheets(WS.Name).Copy
ActiveWorkbook.SaveAs Filename:="C:\" & WS.Name & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next
End Sub


Sub SaveWorkBookasTXT()
'Save all sheets in workbook and combine into one .txt file
Dim WS As Worksheet
Dim FS, A

Set FS = CreateObject("Scripting.FileSystemObject")

'cheek for folder exist and/or create
If FS.FolderExists("C:\TempOut") = False Then FS.CreateFolder
("C:\TempOut")

'save .txt files
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each WS In ThisWorkbook.Worksheets
Sheets(WS.Name).Select
Sheets(WS.Name).Copy
ActiveWorkbook.SaveAs Filename:="C:\TempOut\" & WS.Name &
".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
Next

'write bat file for file combine
Set A = FS.CreateTextFile("C:\TempOut\Combine.bat", True)
A.WriteLine ("type C:\TempOut\*.txt > C:\Output.txt")
A.Close
'combine files
Shell "C:\TempOut\Combine.bat", vbNormalFocus
Application.Wait Now() + TimeValue("00:00:5")
'Kill tempory Dir
FS.deletefolder "C:\TempOut"

End Sub

'Change any .txt to .csv and FileFormat:=xlText to
FileFormat:=xlCSVWindows
cheers


--
D_Rennie
------------------------------------------------------------------------
D_Rennie's Profile: 1412
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=179039

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

First  |  Prev  | 
Pages: 1 2
Prev: using cells property
Next: Environ("Username") with Windows7 64bit