Creating properly formatted text file from vbscript using excel data

[msnews.microsoft.com]

I have an excel spreadsheet named test.xls with A1:B2 populated this way:

A B
~ ~
1 2
3 4

What I want to do from VBScript is copy that data and export it to a tab
deliminated text file.

For example, I can create a document called hello.txt and in it, it would
have
1 2
3 4


I just started playing around with this and have the following so far:
'================================
Dim Xl
Dim myData

myData=""

Set Xl = CreateObject("Excel.Application")
Xl.Workbooks.Open("C:\test.xls")
Xl.Sheets("Sheet1").Select

for each r in Xl.Range("A1","B2")
myData = myData & r.value & vbTab
next

msgbox myData

Set Xl = Nothing
'================================

I haven't created the text file yet (that's trivial). The problem is that
the output comes as follows:

1 2 3 4

 

[Mike Tomasura]

You can save an excel file as tab delimited after you open it.


ChDir "C:\WINDOWS\Desktop"
ActiveWorkbook.SaveAs FileName:="C:\WINDOWS\Desktop\Blank Excel1.txt", _
FileFormat:=xlText, CreateBackup:=False

 

[msnews.microsoft.com]

Thanks Mike. The problem is that I have 10 worksheets in the workbook and
want to save each worksheet with a separate name (use the sheet name for
file name and add .txt). Also each worksheet has extra rows and columns
surrounding the actual data I want to copy to the text file.

I figured it would be easier to just extract the data from vbscript and
creating the text file dynamically, but if there is a way to save a
worksheet as a tab delimited file, that would be great. I can just copy the
range of data to a new worksheet and save that worksheet.