WRITING TO A TEXT FILE WITH SPECIFIC FORMAT

  • Thread starter Thread starter trangp65
  • Start date Start date
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
..
..
..
..
 
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
 
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.
 
Back
Top