Create a Fixed-Width Text File with an Excel VBA Macro
Question details
The user wants to export worksheet data without headers to a fixed-width text file using an Excel VBA macro.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains records that must be exported with fields at specified positions, space padding, and formatted numeric values.
- Observed behavior
- The desired output is one fixed-width line per data row, with no header line and each field formatted and padded to its assigned width.
Confirm the source columns, the first data row, each field's start position and width, and the required numeric and text-file formats. The example below assumes headers are in row 1 and column A contains a value for every record.
Run a VBA Export Procedure from a Standard Module
Use this approach to write one padded, fixed-width line for each worksheet row without exporting headers.
The example exports column A as 20 characters starting at position 1, column B as 8 characters starting at position 21, and column C as 12 characters starting at position 29. Text is left-aligned; numeric fields are right-aligned and formatted with zero and two decimal places, respectively.
Fields are adjacent and padded with spaces; there is no separate delimiter between them. Adjust the columns, widths, and formats to match your file specification.
In desktop Excel, open the Visual Basic Editor from Developer > Visual Basic, then choose Insert > Module. Do not place the procedure in a Worksheet_SelectionChange event.
Paste this code into the module: Option Explicit Public Sub ExportFixedWidth() Dim ws As Worksheet Dim lastRow As Long, r As Long Dim outputPath As Variant Dim fileNum As Integer Dim fileOpen As Boolean Dim lineText As String Dim errorText As String Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then MsgBox "No data rows found." Exit Sub End If outputPath = Application.GetSaveAsFilename( _ InitialFileName:="export.txt", _ FileFilter:="Text Files (*.txt), *.txt") If VarType(outputPath) = vbBoolean Then Exit Sub On Error GoTo ExportError fileNum = FreeFile Open CStr(outputPath) For Output As #fileNum fileOpen = True For r = 2 To lastRow If Not IsNumeric(ws.Cells(r, 2).Value) Or _ Not IsNumeric(ws.Cells(r, 3).Value) Then Err.Raise vbObjectError + 1000, , "Invalid number in row " & r End If lineText = PadField(CStr(ws.Cells(r, 1).Value), 20, False) & _ PadField(Format$(ws.Cells(r, 2).Value, "0"), 8, True) & _ PadField(Format$(ws.Cells(r, 3).Value, "0.00"), 12, True) Print #fileNum, lineText Next r Close #fileNum fileOpen = False MsgBox "Export complete: " & CStr(outputPath) Exit Sub ExportError: errorText = Err.Description If fileOpen Then Close #fileNum MsgBox "Export stopped: " & errorText End Sub Private Function PadField(ByVal fieldValue As String, _ ByVal fieldWidth As Long, _ ByVal alignRight As Boolean) As String If Len(fieldValue) > fieldWidth Then Err.Raise vbObjectError + 1001, , "Field exceeds width " & fieldWidth & ": " & fieldValue End If If alignRight Then PadField = Space$(fieldWidth - Len(fieldValue)) & fieldValue Else PadField = fieldValue & Space$(fieldWidth - Len(fieldValue)) End If End Function
Activate the source worksheet, then run ExportFixedWidth from Developer > Macros. Choose a text-file location and inspect the output in a text editor to verify field positions and numeric formatting.

Prepare Spreadsheet Data with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for reviewing and editing spreadsheet data. Its Microsoft Office format support helps you move existing workbooks into WPS, although it does not fix an Excel VBA macro.

Frequently Asked Questions
How does the macro skip the header row?
The loop starts at row 2 with For r = 2 To lastRow. Change the starting row if your data begins elsewhere.
How do I change the field widths or source columns?
In the lineText assignment, change the column index in ws.Cells(r, column) and the width passed to PadField. Recalculate later field positions after changing a width.
Why does Excel reject my column reference?
Use a quoted column letter, such as ws.Cells(r, "B"), or a numeric column index, such as ws.Cells(r, 2). The unquoted value 1,9 is not a valid single column address.
What happens if a value is too long for its field?
PadField raises an error instead of silently truncating the value. Correct the source value or increase the field width, then run ExportFixedWidth again.
Will the output always use a period as the decimal separator?
Not necessarily. VBA's Format function can follow system regional settings. Check the exported file against the receiving system's required decimal separator.




