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?

Hello,

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

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

Hi Mainframer

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mainframer" wrote in message ...
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?

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.

"Ron de Bruin" wrote:

Hi Mainframer

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Mainframer" wrote in message ...
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?
.

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

"D_Rennie" wrote:


Hello,

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

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

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/sh...d.php?t=179039

Microsoft Office Help