Tag Archives: Fixed-length

Creating a fixedwidth text file in excel using macro

To the data if you know the width that need to be specified set the columnwidths for each column, if you want all the columns to a fixed width you can select all the data columns and set theri their widths.

  Select the Developer>Visual Basic in the excel file

select insert module and write the method

Eg:-


Sub ExportText()
    ' Modify as needed
    Const strFile = "C:\Export\Test.txt"

    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Dim f As Long
    Dim lngMaxWidth As Long
    Dim strLine As String
    Dim lngColWidth As Long

    m = Cells.Find(What:="*", SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    n = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Column
    ReDim lngWidths(1 To n) As Long
    For c = 1 To n
       ' lngMaxWidth = 0
      '  For r = 1 To 100
       '     lngColWidth = Cells(r, c).ColumnWidth
        '    If Len(Cells(r, c).Text) > lngMaxWidth Then
         '       lngMaxWidth = Len(Cells(r, c).Text)
          '  End If
        'Next r
       
       
        lngWidths(c) = Cells(1, c).ColumnWidth
        'lngMaxWidth + 1
    Next c
    f = FreeFile
    Open strFile For Output As #f
    For r = 1 To m
        strLine = ""
        For c = 1 To n
            strLine = strLine & Cells(r, c).Text & _
                Space(lngWidths(c) - Len(Cells(r, c).Text))
        Next c
        Print #f, strLine
    Next r
    Close #f
End Sub