Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Creating/loading a fixed width text file

written by Mark Rowlinson - Last updated Oct 2008

Sometimes we might want to generate a fixed width text file from an excel worksheet. The following procedure will accomplish this having been passed the filename, worksheet to apply it to and a zero-based array of the fixed widths. 

 
Sub CreateFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer) 
    Dim i As Long, j As Long 
    Dim strLine As String, strCell As String 
     
    'get a freefile 
    Dim fNum As Long 
    fNum = FreeFile 
     
    'open the textfile 
    Open strFile For Output As fNum 
    'loop from first To last row 
    'use 2 rather than 1 To ignore header row 
    For i = 1 To ws.Range("a65536").end(xlUp).Row 
        'new line 
        strLine = "" 
        'loop through each field 
        For j = 0 To UBound(s) 
            'make sure we only take chars up To length of field (may want To Output some sort of error If it Is longer than field) 
            strCell = Left$(ws.Cells(i, j+1).Value, s(j)) 
            'add on String of spaces With length equal To the difference In length between field length And value length 
            strLine = strLine & strCell & String$(s(j) - Len(strCell), Chr$(32)) 
        Next j 
        'write the line To the file 
        Print #fNum, strLine 
    Next i 
    'close the file 
    Close #fNum 
     
End Sub
For example you could call it using: 
 
Sub CreateFile() 
    Dim sPath As String 
    sPath = Application.GetSaveAsFilename("", "Text Files,*.txt") 
    If LCase$(sPath) = "false" Then Exit Sub 
    'specify the widths of our fields 
    'the number of columns Is the number specified In the line below +1 
    Dim s(6) As Integer 
    'starting at 0 specify the width of each column 
    s(0) = 21 
    s(1) = 9 
    s(2) = 15 
    s(3) = 11 
    s(4) = 12 
    s(5) = 10 
    s(6) = 186 
'for example To use 3 columns With field of length 5, 10 And 15 you would use: 
    'dim s(2) As Integer 
    's(0)=5 
    's(1)=10 
    's(2)=15 
    'write To file the data from the activesheet 
    CreateFixedWidthFile sPath, ActiveSheet, s 
End Sub
In a similar manner it's also easy to read in a fixed width textfile. Example code is given below (though Excel's built in text to columns feature could also be used). 
 
Sub LoadFile() 
     
    Dim sPath As String 
    sPath = Application.GetOpenFilename() 
    If LCase$(sPath) = "false" Then Exit Sub 
    'specify the widths of our fields 
    'the number of columns Is the number specified In the line below +1 
    Dim s(6) As Integer 
    'starting at 0 specify the width of each column 
    s(0) = 12 
    s(1) = 6 
    s(2) = 2 
    s(3) = 2 
    s(4) = 1 
    s(5) = 8 
    s(6) = 1 
'for example To use 3 columns With field of length 5, 10 And 15 you would use: 
    'dim s(2) As Integer 
    's(0)=5 
    's(1)=10 
    's(2)=15 
    'write To file the data from the activesheet 
    LoadFixedWidthFile sPath, ActiveSheet, s 
     
End Sub 
 
 
Sub LoadFixedWidthFile(strFile As String, ws As Worksheet, s() As Integer) 
     
    Dim i As Long, j As Long 
    Dim strLine As String 
    Const SKIPROWS = 0 'set To 1 To skip first row 
     
    'get a freefile 
    Dim fNum As Long 
    fNum = FreeFile 
     
    'open the textfile 
    Open strFile For Input As fNum 
    'loop from first To last row 
    i = 1 + SKIPROWS 
    While Not EOF(fNum) 
         
        Line Input #fNum, strLine 
        For j = 0 To UBound(s) 
            ws.Cells(i, j + 1).Value = Left$(strLine, s(j)) 
            strLine = Mid$(strLine, s(j) + 1) 
        Next j 
        i = i + 1 
        Application.StatusBar = "Processing line " & i & "..." 
    Wend 
    Close #fNum 
    Application.StatusBar = FALSE 
     
End Sub