Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Save as fixed width text file (Excel 2002)

    Hi

    I Would like some help with a macro to output the content of each tab in the attached worksheet to a (fixed width) text file, the width sizes are in row 1

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save as fixed width text file (Excel 2002)

    The following macro will export each rows 3 and down from each worksheet to a separate text file. The files will have the name of the worksheet followed by .txt, and will be stored in the folder specified in strPath. Change strPath as needed, but make sure it ends in a backslash .

    Sub ExportFixed()
    ' Modify as needed, keep trailing backslash
    Const strPath = "F:Excel"
    Dim wsh As Worksheet
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngMaxRow As Long
    Dim lngMaxCol As Long
    Dim f As Integer
    Dim strOutput As String

    For Each wsh In ActiveWorkbook.Worksheets
    f = FreeFile
    Open strPath & wsh.Name & ".txt" For Output As #f
    lngMaxRow = wsh.Range("A65536").End(xlUp).Row
    lngMaxCol = wsh.Range("IV1").End(xlToLeft).Column
    For lngRow = 3 To lngMaxRow
    strOutput = ""
    For lngCol = 1 To lngMaxCol
    strOutput = strOutput & Left(wsh.Cells(lngRow, lngCol) & _
    Space(255), wsh.Cells(1, lngCol))
    Next lngCol
    Print #f, strOutput
    Next lngRow
    Close #f
    Next wsh

    Set wsh = Nothing
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Save as fixed width text file (Excel 2002)

    Hi Hans

    Thanks for your prompt reply I will test your macro and respond later.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Save as fixed width text file (Excel 2002)

    HI Hans

    That worked very nicely

    Thanks very much

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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