Thread: Saving xls file as fixed width

Given the usual way of setting column widths and saving to prn does not work you will have to use VBA to write the file.

Given the usual way of setting column widths and saving to prn does not work you will have to use VBA to wrhttp://www.exceltip.com/exceltips.phpite the file.

Here is some code to get you started.

Code:

Sub ExportFixedWidth()

    Dim rngData As Range
    Dim lngCol As Long
    Dim lngRow As Long
    Dim strTemp As String
    Dim intUnit As Integer
    Dim lngWidths(1 To 3) As Long
    Dim strValue As String
    Dim lngPos As Long
    
    lngWidths(1) = 10
    lngWidths(2) = 20
    lngWidths(3) = 300
    Set rngData = ActiveSheet.Range("A1").CurrentRegion
    intUnit = FreeFile
    Open ThisWorkbook.Path & "\MyFile.dat" For Output As intUnit
    
    For lngRow = 1 To rngData.Rows.Count
        strTemp = ""
        For lngCol = 1 To rngData.Columns.Count
            If Len(rngData.Cells(lngRow, lngCol).Value) > 255 Then
                lngPos = 1
                strValue = ""
                Do While lngPos <= Len(rngData.Cells(lngRow, lngCol).Value)
                    strValue = strValue & rngData.Cells(lngRow, lngCol).Characters(lngPos, 255).Text
                    lngPos = lngPos + 255
                Loop
            Else
                strValue = rngData.Cells(lngRow, lngCol).Value
            End If
            strTemp = strTemp & strValue & Space(lngWidths(lngCol) - Len(strValue))
        Next
        Print #intUnit, strTemp
    Next
    Close intUnit
    
End Sub