All

I have an Excel sheet that I need to be able to export in a particular way -
This will be extracted to a text file (csv).

What I need to be able to do is apply the following criteria to the data before
it is extracted. If any data within column C is over 20 characters long only
export the first 20 characters. Also, if the data is less than 20 - for
example 7 apply 13 spaces to the data to ensure that 20 characters worth are
extracted in the csv file.

Appreciate any help you can offer. thanks - Al ( )

Al

This is my reply to a similar problem where all the fields needed to be a
fixed length. Perhaps you can adapt this?

Sub WriteFixedFile()
Dim LastRow As Long
Dim i As Long
Dim sRecord As String
LastRow = Range("A65536").End(xlUp).Row
Open "c:\TESTFILE" For Output As #1 ' Open file for output.
For i = 1 To LastRow
sRecord = Range("A" & i).Value & Space(15 - Len(Range("A" & i)))
sRecord = sRecord & Range("B" & i).Value & Space(5 - Len(Range("B" &
i)))
sRecord = sRecord & Range("C" & i).Value & Space(10 - Len(Range("C" &
i)))
sRecord = sRecord & Range("D" & i).Value & Space(15 - Len(Range("D" &
i)))
'Write #1, sRecord ' data surrounded by quotes
Print #1, sRecord ' no quotes
Next 'i
Close #1
End Sub

You might need to do some calculation as you want to truncate the length.
The assumption with the above code was that the data would always be less in
length than the maximum size of the fields.

Regards

Trevor



"MrAlMackay" wrote in message
...
All

I have an Excel sheet that I need to be able to export in a particular
way -
This will be extracted to a text file (csv).

What I need to be able to do is apply the following criteria to the data
before
it is extracted. If any data within column C is over 20 characters long
only
export the first 20 characters. Also, if the data is less than 20 - for
example 7 apply 13 spaces to the data to ensure that 20 characters worth
are
extracted in the csv file.

Appreciate any help you can offer. thanks - Al ( )