Store Worksheets from a Workbook as Text files

[VishalkumarT]

Dear Friends

I have a Workbook and it contains 10 Worksheets.
(for example, c:\wb.xls)

So how can I store separate worksheets from this Workbook in a folder, with names like 1.xls, 2.xls ... 10.xls
(e.g. c:\ws\1.xls, c:\ws\2.xls ... c:\ws\10.xls)

And then I want to convert this worksheets as text files, I mean 1.txt, 2.txt ... 10.txt
(e.g. c:\ws\1.txt, c:\ws\2.txt ... c:\ws\10.txt)

Thanks for your replies in advance.

Regards
Vishalkumar

 

[bigoldbulldog]

How about:

Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets
s.SaveAs Filename:=s.Name, FileFormat:=xlTextMSDOS
Next s

' Chew on the s.Name or increment an integer for 1.txt, etc.

Cheers,
ND

 

[VishalkumarT]

Hello Friend

This is really a good help, but here I have one question, I think in this method, it takes some default file saving path, so how can I provide my own path.

I mean in the line,
s.SaveAs Filename:=s.Name, FileFormat:=xlTextMSDOS

I want to provide my own path to save the files...How can I do this.

If I use the format
s.SaveAs Filename:="c:\my folder\s.Name"
then it will overwrite all extracted files with the same name 's.Name' and some unknown format,e.g. s.Name.***

Thank you for your reply again, in advance.

Best Regards
Vishalkumar

 

[VishalkumarT]

Dear Friends

I think I have solved this problem

And thanks for your time to read my problem and reply.

Best Regards
Vishalkumar