WRITING TO A TEXT FILE WITH SPECIFIC FORMAT

T

trangp65

Help please!
I need to take info from an excel spreadsheet and put it
into a text file. The text file has to have a specific
length and format (not comma delimited). Each row would
start with the date and follow with 24 values with a space
between (fixed format, f 5.0). I have found books talk
about writing with comma delimited, but not fixed format.

Any help or examples would be greatly appreciated. I would
like to do this with VBA.

010103 xxxxx xxxxx xxxxx ...up to 24
010203 xxxxx xxxxx xxxxx ...up to 24
..
..
..
..
 
P

pikus

How is the information arranged in Excel?
Each line in a cell?
Each five-character set in a cell?

Also, will we be creating a new text file or using an existing one?
If it's pre-existing, will we be adding to the current contents or
adding to it?
Will we be adding the text at the start or end of the file?

- Pikus
 
J

J.E. McGimpsey

One way:

Public Sub FixedField()
Const DELIMITER As String = " "
Const cFILL As String = "00000"
Dim rRecord As Range
Dim rField As Range
Dim nLen As Long
Dim sOut As String

nLen = Len(cFILL)
Open "Test.txt" For Output As #1
For Each rRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rRecord
sOut = Format(.Cells(1), "mmddyy")
For Each rField In Range(.Cells(1, 2), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & _
Right(cFILL & rField.Text, nLen)
Next rField
Print #1, sOut
End With
Next rRecord
Close #1
End Sub

you can change the fill characters by changing cFILL, for example,
to 5 spaces, or 5 underscores, etc. The field width will be the
number of characters you enter in cFILL.
 
Top