Link to home
Start Free TrialLog in
Avatar of drezner7
drezner7

asked on

Read Excel Sheet and Output to Text

I would like to read an excel file and output the data into Text.

Excel Spreadsheet format
Col A       Col B         Col C                          Col D
BBA         CV           123456/2-24                 789/221-5

Output to Text File:

BBA CV        123456/2-24                 789/221-5

Note each Column has specific spaces in the text file as noted there is one space between Col A and Col B, but there are 8 spaces between Col B and Col C...

Can you please help... Here is what I have so far:

onst sExcel = "C:\Test\Set.xlsx"
Const sXML = "C:\Output\Set.txt"
Const xlUp = -4162

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(sExcel, False, False)
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sXML, True)

I can use: oOut.WriteLine for every column, but I am not sure how to space the data correctly.
ASKER CERTIFIED SOLUTION
Avatar of rlandquist
rlandquist
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of drezner7
drezner7

ASKER

Thank you very much worked like a charm