left or right justify text exported to text file

[docaia]

Hello Experts,
I am trying to do some calculations on data in excel sheet and then export them in a standardized form of a text file.
to do this I used the file system object (adding the Microsoft script runtime reference from vb tools menu) the following code works fine with me..except that I cannot get the output right or left justified

Sub exporttext()
Dim i As Integer
Dim fso As FileSystemObject
Dim hope As TextStream
Set fso = New FileSystemObject
Set hope = fso.CreateTextFile("D:\hope.txt", True)
With hope
For i = 1 To 10
.WriteLine (Cells(i, 1) & Space(4) & Cells(i, 2))
Next i
.Close
End With
Dim x
    x = Shell("notepad.exe d:\hope.txt", 1)
End Sub

this exports the data in the following form:

123    345
3245    325
43    235
43    3245
2325    3190
1516.4    4051
1636.6    4912
1756.8    5773
1877    6634
1997.2    7495

I need to have the first set always left justified, while the 2nd set of numbers to be always right justified, beginning from column 30 in the text file up to column 37....in other words...this is my desired output:

123                              345
3245                             325
43                               235
43                              3245
2325                            3190
1516.4                          4051
1636.6                          4912
1756.8                          5773
1877                            6634
1997.2                          7495

the second thing I may have a problem with is the number of rows is not fixed...so I want the loop to include all the rows, should I use something line for i = 1 to endxlUp? or what is the best function for that?
your help is much appreciated
thanks and regards.

[docaia]

I found this solution...not sure if it will work generally on everything or not

Sub exporttext()
Dim i As Integer
Dim fso As FileSystemObject
Dim hope As TextStream
Set fso = New FileSystemObject
Set hope = fso.CreateTextFile("D:\hope.txt", True)
With hope
For i = 1 To 10
.WriteLine (Cells(i, 1) & Space(37 - Len(Cells(i, 1)) - Len(Cells(i, 2))) & Cells(i, 2))
Next i
.Close
End With
Dim x
    x = Shell("notepad.exe d:\hope.txt", 1)
End Sub

any advice is welcomed

[John_w]

Use the Format function to left- or right-justify the numbers, and a while loop to check for end of data, like this:

Sub exporttext()
    Dim i As Integer
    Dim fso As FileSystemObject
    Dim hope As TextStream
    
    Set fso = New FileSystemObject
    Set hope = fso.CreateTextFile("c:\hope.txt", True)
    With hope
        i = 1
        While Cells(i, 1).Value <> ""
            .WriteLine Format(Cells(i, 1), "!" & String(10, "@")) & Format(Cells(i, 2), String(20, "@"))
            i = i + 1
        Wend
        .Close
    End With
    
    Dim x
    x = Shell("notepad.exe c:\hope.txt", 1)
End Sub

Adjust the field widths (10 and 20 above) as required.