There are several ways to do this, but personally, I like to control what get
written to the file.
One approach would be to write a VBA macro that opens each of the files and
writes out EACH SHEET as a separate text file,
then... I believe there is a syntax for the DOS copy command (/b?) that I used
many, many, many years ago.
that would allow you to "concatenate" the text files.
However, another approach would be to use VBA to open a text file,
then process each file and sheet in turn.
I wrote this script and tested it out.
it puts a "|" delimiter between each cell.
and processed files with multiple sheets.
It wrote 418,658 lines containing 75 columns of data
in 8 minutes and 37 seconds.
perhaps it'll help:
(Paul)
'----------------------------------------------------------------
Option Explicit
Public Const ForReading = 1, ForWriting = 2, ForAppending = 8
Sub Write_Data_to_TXT()
Dim fso, f, File, Files, fName, FullName, fEXT
Dim RepName, fPath, fRep
Dim I, R, C, rMax, cMax, deLim
Dim tStart, tStop, tMin, tSec, reccnt
'-----------------------------
tStart = Now()
reccnt = 0
fPath = "C:\temp\files\"
RepName = "Report.txt"
deLim = "|"
'-----------------------------
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.folderexists(fPath)) Then
Set fRep = fso.OpenTextFile(fPath & RepName, ForWriting, True)
'-----------------------------------------------------------------
On Error Resume Next
Application.ScreenUpdating = False
Set f = fso.getfolder(fPath)
Set Files = f.Files
For Each File In Files
fName = File.Name
FullName = File.Path
fEXT = UCase(fso.GetextensionName(File.Path))
If (Left(fEXT, 3) = "XLS") Then
Application.StatusBar = File.Name
Workbooks.Open Filename:=FullName, ReadOnly:=True
For I = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(I).Select
rMax = ActiveCell.SpecialCells(xlLastCell).Row
cMax = ActiveCell.SpecialCells(xlLastCell).Column
For R = 1 To rMax
If (R Mod 1000 = 0) Then Application.StatusBar = fName
& ": " & R & " of & rmax"
For C = 1 To cMax
fRep.write ActiveWorkbook.Sheets(I).Cells(R,
C).Value & deLim
If (Err.Number <> 0) Then
Err.Clear
fRep.write deLim
End If
Next C
fRep.writeline
reccnt = reccnt + 1
Next R
Next I
Workbooks(fName).Close savechanges:=False
End If
Next File
fRep.Close
End If
tStop = Now()
tSec = DateDiff("s", tStart, tStop)
tMin = Int(tSec / 60)
tSec = tSec - tMin * 60
MsgBox "Wrote " & reccnt & " Records in" & Chr(13) & tMin & " Minutes, " &
tSec & " seconds"
Application.StatusBar = False
Application.ScreenUpdating = False
On Error GoTo 0
End Sub
________________________________
From: Vikas Chouhan <vikask...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Wed, March 17, 2010 5:05:10 AM
Subject: $$Excel-Macros$$ how to convert excel into .txt
Hi,
if there is ten excel file in a folder, how to convert these files's into one
.txt file
One Team One Dream One Goal
Warm Regards,
Vikas Chauhan
E-Mail :- vikask...@gmail.com,vikask...@rediffmail.com,
9911868518,
"We can't Spell S_ccess without U"
Life is Very beautiful !!!
¨`•.•´¨) Always
`•.¸(¨`•.•´¨) Keep
(¨`•.•´¨)¸.•´ Smiling!
`•.¸.•´.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
