We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Read Excel Sheet and Output to Text

drezner7
drezner7 asked
on
Medium Priority
358 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you very much worked like a charm
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.