left or right justify text exported to text file

docaia

New Member
Joined
Feb 28, 2008
Messages
38
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
Code:
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:
Code:
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:
Code:
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I found this solution...not sure if it will work generally on everything or not
Code:
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
 
Upvote 0
Use the Format function to left- or right-justify the numbers, and a while loop to check for end of data, like this:
Code:
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.
 
Upvote 0
Hi,

Its a very old post and this code is so helpful. I would like to know if any of the cell is blank then the text file field position is replaced ?
 
Upvote 0
I don't know what you mean by 'replaced', but if you mean 'maintained' then change the WriteLine to:
Code:
            .WriteLine IIf(Cells(i, 1).Value <> "", Format(Cells(i, 1), "!" & String(10, "@")), String(10, " ")) & _
                       IIf(Cells(i, 2).Value <> "", Format(Cells(i, 2), String(20, "@")), String(20, " "))
 
Upvote 0
Hi John,

Yes, I meant maintaining the field positions. :)Thank you so much for such a genius code, I wasn't aware of IIF in VBA (learnt a great function). You really know how to harness the power of VBA. We all learn great things from leaders :) Cheers :)
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top