Hi,

I have a code below to export data to a text file for reports, I am having
problems as to the export I require, the details are at the bottom of the

page, I need to export a total of 12 characters per cell, either cut text or
padded out with spaces so when it exports into text file it is exported to

look like it's in columns and not all mixed about unreadable.

Please see the data example and export example at the bottom of the page


Sub WriteFixed()

Const MyPath = "C:\temp\"
Const WriteFileName = "text.txt"

Const ForReading = 1, ForWriting = 2, ForAppending = 3

Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")

'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
OutPutLine = ""
For Colcount = 1 To LastCol
Data = Cells(RowCount, Colcount).Text
If Len(Data) < 12 Then
Data = Data & WorksheetFunction.Rept(" ", 12 - Len(Data))
else
Data = left(Data,12)
End If
if OutPutline < "" then
OutPutLine = OutPutLine & " "
end if
OutPutLine = OutPutLine & Data
Next Colcount
tswrite.writeline OutPutLine
Next RowCount

tswrite.Close

End Sub


This is the data.

Field 1 Field 2 Field 3 Field 4 and so on possibly to the end
Title Title Title Title Title

Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name Name (these could be more than 12 character but need to
cut the export to 12 characters and if the cell

doesn't have 12 characters it needs to be padded out with spaces at the end
so it can exprt properly.e.g.[Name ]

When it exports to a text file it needs to be displayed the same as above as
if in columns so it can be read.
What I am getting is like this

NameNameNameNameName
Name Name Name Name Name
Name Name Name Name Name
Name Name Name Name

Hope this is helpful.

Regards

Mark