Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for export
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to Export Data from Chart | Excel Discussion (Misc queries) | |||
Export worksheet in New File by VBA code | Excel Programming | |||
Need Help With Export Code | Excel Programming | |||
Request Vba code Export gif file | Excel Programming | |||
Export a form via code? | Excel Programming |