S
santaviga
Hi to all.
I have input the code and it works to an extent, when it runs
the macro to export the data to text file, the text is still out of
alignment like
da da da da
da da da da
instead of should be like this
da da da da
da da da da
when the data exports to a text file in needs to be in columns as theres a
lot of data
but it can only max of 12 characters long and the data less than 12
characters needs
to be padded out in spaces
Many thanks please see the code below
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
I have input the code and it works to an extent, when it runs
the macro to export the data to text file, the text is still out of
alignment like
da da da da
da da da da
instead of should be like this
da da da da
da da da da
when the data exports to a text file in needs to be in columns as theres a
lot of data
but it can only max of 12 characters long and the data less than 12
characters needs
to be padded out in spaces
Many thanks please see the code below
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