I've set up fixed length fields. My data is right justified. I wantto be
able to save to a text file and retain the data with fixed length fields,
with leading spaces to pad the right justified data so everything lines up
the way I entered it.
couldn't find a way to have excel format it, so here are some
solutions I came up with.
in the following, you would still need to decide what to do when the
contents are longer than the fixed width specified... i'll leave that
up to you.
(1) could have a worksheet that references the other and adds the
padding via formulas. Then save that sheet as the text file.
=REPT(" ", $A$1 - LEN(Sheet1!A2)) & Sheet1!A2
$A$1 is the fixed width
(2) vba routine to sweep a specified sheet and make each cell fixed
width
Sub processSheets()
Const FIXED_WIDTH As Long = 10
Dim sh As Worksheet
'could loop for all sheets
Set sh = ActiveSheet
applyFixedWidth FIXED_WIDTH, sh
End Sub
Sub applyFixedWidth(fixedWidth As Integer, sh As Worksheet)
Dim rng As Range
Dim elem As Range
Dim length As Long
If (fixedWidth < 0) Then Exit Sub
For Each elem In sh.UsedRange
length = Len(elem.Value)
If (length < fixedWidth) Then
elem.Value = Space(fixedWidth - length) + elem.Value
Else
'???
End If
Next
End Sub
