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.
drezner7Asked:
Who is Participating?
 
rlandquistConnect With a Mentor Commented:
Give this a try.  You can adjust the number of spaces needed in the quotes on line  23

Let me know if you have any questions.
sExcel = "C:\temp\Set.xlsx"
sXML = "C:\temp\Set.txt"

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)

'Start with row 2 of spreadsheet assuming first row has column headings
iRow = 2

'Read each row of the spreadsheet until a blank value is encountered in
'column 1 
Do While oSheet.Cells(iRow, 1).Value <> ""
    strA = Trim(oSheet.Cells(iRow, 1).Value)
    strB = Trim(oSheet.Cells(iRow, 2).Value)
    strC = Trim(oSheet.Cells(iRow, 3).Value)
    strD = Trim(oSheet.Cells(iRow, 4).Value)
    
    oOut.WriteLine strA & " " & strB & "        " & strC & " " & strD
        
    iRow=iRow+1
Loop

oExcel.quit
oOut.Close

Open in new window

0
 
drezner7Author Commented:
Thank you very much worked like a charm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.