Code for export

S

santaviga

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
 
J

Joel

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 said:
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
Click to expand...
 
S

santaviga

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 said:
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 said:
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
Click to expand...
Click to expand...
 
J

Joel

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

Joel Dot Warburg At ITT Dot COM

santaviga said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

santaviga

Thanks Joel its on its way to you.

Many thanks

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

Joel Dot Warburg At ITT Dot COM

santaviga said:
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 said:
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)


:

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
Click to expand...
Click to expand...
Click to expand...
 
S

santaviga

Thanks a lot Joel

Many Thanks

Mark

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

Joel Dot Warburg At ITT Dot COM

santaviga said:
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 said:
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)


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with code 1
Add Column Names to VBA script 1
Need help combining 5
setting delimiters 2
VBA CreateNames (ignores last 5 rows?) 6
Joel - Importing multiple text files to 1 spreadsheet, now importing from excel files 7
Help Change existing Code 6
need rons help on email.(recall temp file?) 3

Top