Macro to export data to fixed width not saving right justifcation
 

Hello All,
I have a macro to export data to a fixed width text file. The macro runs
fine but when I open up the txt file I noticed that three fields that I had
right justified are left justified in the txt file.
This is my macro. There seems like there should be an easy way to keep the
justification but I have not been able to find it when searching. Any help
you can offer would be appreciated. Thanks for your time. Here is the macro
code:

Sub MakeFixedWidth()

Dim Sizes As Variant
Dim arr As Variant
Dim r As Long, c As Long
Dim fso As Object
Dim ts As Object
Dim TheLine As String
Dim TestStr As String

Sizes = Array(9, 20, 20, 1, 1, 30, 30, 20, 2, 9, 8, 8, 1, 5, 2, 10, 11, 1,
7, 7, 8)

arr = ActiveSheet.UsedRange

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\filename.txt", True)

For r = 1 To UBound(arr, 1)
TheLine = ""
For c = 1 To UBound(arr, 2)
TestStr = Left(Trim(CStr(arr(r, c))), Sizes(c - 1))
TheLine = TheLine & TestStr & String(Sizes(c - 1) - Len(TestStr), " ")
Next c
ts.WriteLine TheLine
Next r

ts.Close

Set ts = Nothing
Set fso = Nothing

MsgBox "Done"

End Sub