Exporting from multiple worksheets to single text file

[Chris Dunigan]

Hi,

I have an Excel file with multiple worksheets (containing over 65,000
rows of data in total). I want to export all the info from all sheets
into a single text file (all the sheets are in the same format).
I can manage to export each sheet into a separate text file using the
following code:

----
Dim SheetCounter As Integer

For SheetCounter = 1 To Sheets.Count
Sheets(SheetCounter).Activate
ChDir "P:\"
ActiveWorkbook.SaveAs filename:= _
"P:\" & ActiveSheet.Name & ".txt" _
, FileFormat:=xlText, CreateBackup:=False
End If
Next
------

Could someone let me know how to export data from multiple sheets into
a single text file.

Many thanks,
Chris Dunigan

 

[keepitcool]

Chris,

keep it simple, as you wont have to do this often:

open a dos box, change to the proper directorry on p:
then type
copy simple*.txt combined.txt

this combines the individual file into 1 larger file.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Chris Dunigan]

Thanks for the quick reply.
The suggestion is great for myself, however the premise of the
spreadsheet is that inexperienced users simply click a button and the
code does everything else. I wonder if there is a way to automate this
task.

Thanks,
Chris

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Here is a reference for how to write code to work with text files:

http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications