Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Export Excel spreadsheet to fixed-width text file

    Hello,

    Excel has the functionality to import fixed-width text files where it presents a dialog box that lets you choose start and end of fields and puts into columns.

    Does it also have functionality where, given an existing spreadsheet, you can export to a fixed-width text file?

    If so, how do I access this? I have tried using Save As and choosing Text File but it seems to only save as Tab-delimited which doesn't help me.

    Required fixed width text file from spreadsheet as under.

    a=2
    b=15
    c=19
    d=4
    e=38
    f=35
    g=13
    h=15
    i=2
    j=10 (if cell empty then to create white space of 10 in the text file)

    Please find attached sample files for details

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Hey Z,

    This is what I have come up with so far. The columns align but I have to get rid of the quotes

    Code:
    Public Sub textfile()
    Dim myFile As String, rng As Range, cellValue As Variant, I As Integer, J As Integer
    Dim s(9) As Integer
        s(0) = 20
        s(1) = 15
        s(2) = 15
        s(3) = 3
        s(4) = 30
        s(5) = 25
        s(6) = 15
        s(7) = 15
        s(8) = 3
    myFile = "C:\Users\Maudibe\Desktop\test6.txt"
    Set rng = ActiveSheet.UsedRange
    Open myFile For Output As #1
    For I = 1 To rng.Rows.Count
         For J = 1 To rng.Columns.Count
            cellValue = rng.Cells(I, J).Value
            cellValue = Left(cellValue, s(J - 1))
            cellValue = cellValue & String(s(J - 1) - Len(cellValue), Chr(32))
            If J = rng.Columns.Count Then
                Write #1, cellValue
            Else
                Write #1, cellValue,
            End If
        Next J
    Next I
    Close #1
    End Sub
    Zmagic.png

    Maud

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Maudibe View Post
    Hey Z,

    This is what I have come up with so far. The columns align but I have to get rid of the quotes


    Maud
    Maud, this is probably no more efficient than your excellent little loop, but this question has reminded me that you can define fixed-width strings in VBA e.g.

    dim s1 As String * 20

    gives you a 20-character fixed-width string which will be automatically padded when you assign a shorter value to it.


    Not sure if this helps but thought I'd throw it in
    Last edited by jeremybarker; 2015-08-13 at 04:21. Reason: corrected syntax

  4. The Following User Says Thank You to jeremybarker For This Useful Post:

    Maudibe (2015-08-13)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I'll see if I can work that in. Thanks Jeremy!

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..in your post#2 code, just change
    Write #1
    to
    Print #1
    ..and you will be amazzzzzzed

    zeddy
    •Excel Invaluable Assistance

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    ..that means replacing both of the Write to Print

    zeddy
    •Excel Valuable Assistance
    .

  8. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2015-08-13)

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Hey Zeddy, you were right on!

Posting Permissions

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