Export multiple Excel sheets to a single text file

[Mainframer]

I want to export (SaveAs) multiple worksheets to a single text file. probably
csv. By Microsoft's design I can't do that with a straight Save As from the
menu. So I thought maybe I could create a macro that would save each sheet
one at a time. Unfortunately this doesn't seem to work because
Workbook.SaveAs doesn't seem to have an append capability. Worse case is I
save each sheet to a separate file and manually append them. Short of that,
any programmatic suggestions?

 

[D_Rennie]

Hello,

Here are two lots of code one will put alll the sheest into sererat
.txt on the c:/ drive.

the other will make a temp drive and split out all the sheets into .tx
files and then combine these into one .txt file C:\Output.txt

 

[Ron de Bruin]

Hi Mainframer

For workbooks see
http://www.rondebruin.nl/mergetotxt.htm

 

[Mainframer]

I'll have to look at this in depth and see how applicable it is to my
question. But for now, on the surface, it looks different. You are talking
about merging multiple workbooks into a text file and I am talking about
merging multiple worksheets to a text file.

Thanks for the reply.

 

[Mainframer]

Did I miss something here? I don't see any code.

 

[D_Rennie]

The way i posted the code, The NG feed wont support php tags from th
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.CreateFolde
("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 t
FileFormat:=xlCSVWindows
cheer