Help with code

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
 

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


Top