Exporting Excel Data to Text File

I have the code to Export an Excel worksheet to a text file, but due to the variable length of the data in the columns, the data in the text file isn't aligned in the proper positions.

Is there a way to set tabs or something like that in the output file, so the data will be placed in fixed positions ?

Thanks for any help...

You could try grabbing all of the needed data into a multidimensional array and then write each section of the array to a text file with say "|" as the delimiter.

Assuming cells A1 to C4 have data needed

Code:

---

Dim xls as object, Data(), i As Byte, Message$
Set xls = CreateObject("Excel.Application")
xls.Visible = True
xls.Workbooks("C:\TestData.xls").Open
Redim Data(1 To 3,1 To 4)
xls.Range("A1").Select
For i = 1 To 4
    Data(1,i) = xls.ActiveCell.Offset(i-1,1).Value
    Data(2,i) = xls.ActiveCell.Offset(i-1,2).Value
    Data(3,i) = xls.ActiveCell.Offset(i-1,3).Value
Next i
xls.Workbooks("Tesdata.xls").Close SaveChanges:=False
xls.Application.Quit
Set xls = Nothing
Open "C:\Testdata.txt" For Output as #1
For i = 1 To 4
    Message = Data(1,i) & "|" & Data(2,i) & "|" & Data(3,i)
    Line Input #1, Message
Next i
Close #1

---

That might do it for you.

hi, think this code will get all data structured in columns with fixed length

Code:

---

Dim s As String 'temp string
Dim iNumCharsInCols As String 'fixed col width

iNumCharsInCols = 35 'max number of chars in one col
s = Space(iNumCharsInCols)

---

'when saving do something like this

Code:

---

For i = 1 To 4
    Message = Left(s & Data(1, i), iNumCharsInCols) & Left(s & Data(2, i), iNumCharsInCols) & Left(s & Data(3, i), iNumCharsInCols)
    Line Input #1, Message
Next i

---

hmmm.. not to good explained... but I hope you get the idea.

If u have questions reg. this.... just ask :)

peet