Creating properly formatted text file from vbscript using excel data
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 |
Creating properly formatted text file from vbscript using excel data
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 |
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 |
All times are GMT +1. The time now is 09:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com