Results 1 to 3 of 3

left or right justify text exported to text file

This is a discussion on left or right justify text exported to text file within the Excel Questions forums, part of the Question Forums category; Hello Experts, I am trying to do some calculations on data in excel sheet and then export them in a ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    37

    Default left or right justify text exported to text file

    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.

  2. #2
    New Member
    Join Date
    Feb 2008
    Posts
    37

    Default Re: left or right justify text exported to text file

    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

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,978

    Default Re: left or right justify text exported to text file

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com