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.Applic ation")
Set oBook = oExcel.Workbooks.Open(sExc el, False, False)
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)
Set oFSO = CreateObject("Scripting.Fi leSystemOb ject")
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.
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.Applic
Set oBook = oExcel.Workbooks.Open(sExc
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)
Set oFSO = CreateObject("Scripting.Fi
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER