Creating properly formatted text file from vbscript using excel data
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.
"Mike Tomasura" wrote in message
...
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" wrote in message
...
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
|