fixed length field - VBA

[missv]

i'm working on a VBA code in which i need to define each field as a specific length, and justify (or pad) the empty spaces. each column needs to be defined a specific length and will not change in the future.

i'm thinking i need to define the range as a string, with a fixed length type? does anyone have a snippet of code or some suggestions on how to write this? i can't imagine it would be terribly hard, but i've already spent a few unsuccessful hours already.

any help is much appreciated!

 

[VoG]

Try something like

Dim s As String * 10

 

[missv]

thanks, i've gotten that far. however, i keep getting errors when i try to define the range(A:A) as s.

will this pad spaces as well?

 

[VoG]

I don't really understand what you want to do. To set the column width

Columns("A").ColumnWidth = 10

but that won't relate directly to a 10 character fixed-length string.

 

[missv]

maybe this will explain a little better. i have 4 columns.

column 1 needs to be fixed width of 10. however, the actual data varies in the length, so i need to pad spaces on the right to get to a fixed width of 10.

column 2 - fixed width of 8.
column 3 - fixed width of 15 - need to pad 0's on the left
column 4 - fixed width of 10 - pad spaces on the left.


i feel like Dim s as String * 10 is correct (and so forth for the other fixed length columns), but i'm getting an error trying to set column A as s.

Last, on column 3, after i set it to fixed width of 15, how would i pad the 0s?

 

[VoG]

Perhaps you can do something like this

Range("B1").Value = "XXXXXXXX"
Columns("B").AutoFit
Range("B1").ClearContents
Range("C1").Value = "XXXXXXXXXXXXXXX"
With Columns("C")
    .AutoFit
    .NumberFormat = "000000000000000"
End With
Range("C1").ClearContents