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

Did you look at my posting on Friday? I think you have leading spaces in the
worksheet data that is giving you the extra spaces.


Make sure the data is in different columns. There should always be at least
5 spaces between each column because of the statement
OutPutLine = OutPutLine & " "

If you are getting less than 5, it means the data is in the same cell. Look
at this data you provided

Name Name Name Name

There is only two spaces between "Name", not 5. This means the data is in
the same column in the worksheet.
change from
Data = Cells(RowCount, Colcount).Text
to
Data = Trim(Cells(RowCount, Colcount).Text)


"santaviga" wrote:

> 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
>

Hi, Sorry I didn't see your posting as I lost it, i'm not recieving emails to
inform me of response. Have tried it now and helps a little but still not in
columns in text file, do you have an e-mail address I can send to you the
file and you can have a look at what i am getting.

Many thanks

"Joel" wrote:

> Did you look at my posting on Friday? I think you have leading spaces in the
> worksheet data that is giving you the extra spaces.
>
>
> Make sure the data is in different columns. There should always be at least
> 5 spaces between each column because of the statement
> OutPutLine = OutPutLine & " "
>
> If you are getting less than 5, it means the data is in the same cell. Look
> at this data you provided
>
> Name Name Name Name
>
> There is only two spaces between "Name", not 5. This means the data is in
> the same column in the worksheet.
> change from
> Data = Cells(RowCount, Colcount).Text
> to
> Data = Trim(Cells(RowCount, Colcount).Text)
>
>
> "santaviga" wrote:
>
> > 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
> >

becaseu of spam I posted my e-mail address as names

Joel Dot Warburg At ITT Dot COM

"santaviga" wrote:

> Hi, Sorry I didn't see your posting as I lost it, i'm not recieving emails to
> inform me of response. Have tried it now and helps a little but still not in
> columns in text file, do you have an e-mail address I can send to you the
> file and you can have a look at what i am getting.
>
> Many thanks
>
> "Joel" wrote:
>
> > Did you look at my posting on Friday? I think you have leading spaces in the
> > worksheet data that is giving you the extra spaces.
> >
> >
> > Make sure the data is in different columns. There should always be at least
> > 5 spaces between each column because of the statement
> > OutPutLine = OutPutLine & " "
> >
> > If you are getting less than 5, it means the data is in the same cell. Look
> > at this data you provided
> >
> > Name Name Name Name
> >
> > There is only two spaces between "Name", not 5. This means the data is in
> > the same column in the worksheet.
> > change from
> > Data = Cells(RowCount, Colcount).Text
> > to
> > Data = Trim(Cells(RowCount, Colcount).Text)
> >
> >
> > "santaviga" wrote:
> >
> > > 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
> > >

Thanks Joel its on its way to you.

Many thanks

"Joel" wrote:

> becaseu of spam I posted my e-mail address as names
>
> Joel Dot Warburg At ITT Dot COM
>
> "santaviga" wrote:
>
> > Hi, Sorry I didn't see your posting as I lost it, i'm not recieving emails to
> > inform me of response. Have tried it now and helps a little but still not in
> > columns in text file, do you have an e-mail address I can send to you the
> > file and you can have a look at what i am getting.
> >
> > Many thanks
> >
> > "Joel" wrote:
> >
> > > Did you look at my posting on Friday? I think you have leading spaces in the
> > > worksheet data that is giving you the extra spaces.
> > >
> > >
> > > Make sure the data is in different columns. There should always be at least
> > > 5 spaces between each column because of the statement
> > > OutPutLine = OutPutLine & " "
> > >
> > > If you are getting less than 5, it means the data is in the same cell. Look
> > > at this data you provided
> > >
> > > Name Name Name Name
> > >
> > > There is only two spaces between "Name", not 5. This means the data is in
> > > the same column in the worksheet.
> > > change from
> > > Data = Cells(RowCount, Colcount).Text
> > > to
> > > Data = Trim(Cells(RowCount, Colcount).Text)
> > >
> > >
> > > "santaviga" wrote:
> > >
> > > > 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
> > > >

Thanks a lot Joel

Many Thanks

Mark

"Joel" wrote:

> becaseu of spam I posted my e-mail address as names
>
> Joel Dot Warburg At ITT Dot COM
>
> "santaviga" wrote:
>
> > Hi, Sorry I didn't see your posting as I lost it, i'm not recieving emails to
> > inform me of response. Have tried it now and helps a little but still not in
> > columns in text file, do you have an e-mail address I can send to you the
> > file and you can have a look at what i am getting.
> >
> > Many thanks
> >
> > "Joel" wrote:
> >
> > > Did you look at my posting on Friday? I think you have leading spaces in the
> > > worksheet data that is giving you the extra spaces.
> > >
> > >
> > > Make sure the data is in different columns. There should always be at least
> > > 5 spaces between each column because of the statement
> > > OutPutLine = OutPutLine & " "
> > >
> > > If you are getting less than 5, it means the data is in the same cell. Look
> > > at this data you provided
> > >
> > > Name Name Name Name
> > >
> > > There is only two spaces between "Name", not 5. This means the data is in
> > > the same column in the worksheet.
> > > change from
> > > Data = Cells(RowCount, Colcount).Text
> > > to
> > > Data = Trim(Cells(RowCount, Colcount).Text)
> > >
> > >
> > > "santaviga" wrote:
> > >
> > > > 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
> > > >