[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Read Excel Sheet and Output to Text

Posted on 2011-04-19
2
Medium Priority
?
337 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.
0
Comment
Question by:drezner7
2 Comments
 
LVL 12

Accepted Solution

by:
rlandquist earned 2000 total points
ID: 35426569
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
 

Author Closing Comment

by:drezner7
ID: 35427661
Thank you very much worked like a charm
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question