While I hate this kind of file, I recently had to re-write an Excel VBA script that wrote the worksheet to a fixed width format file. It isn't perfect, but this is what I have so far:
; #FUNCTION# ====================================================================================================================
; Name ..........: _Export_FixedWidth
; Description ...: Exports an array to a file using a fixed width format
; Syntax ........: _Export_FixedWidth($avData, $aiWidth, $aiPrecision, $sFile[, $sNewLine = Default])
; Parameters ....: $avData - a 2D array of variants.
; $aiWidth - a 1D array of integers.
; $aiPrecision - a 1D array of integers. Default copies $aiWidth.
; $sFile - full file path to save as or a file handle.
; $sNewLine - [optional] newline. Default is @CRLF.
; Return values .: Success - True
; Failure - False and sets @error:
; |1 - $avData isn't a 2D array
; |2 - $avData has more columns than (@extended) 1 - $aiWidth or 2 - $aiPrecision
; |3 - Unable to write data to $sFile
; Author ........: Seadoggie01
; Modified ......: December 17, 2020
; Remarks .......: $aiWidth's and $aiPrecision's sizes must match $avData's column count.
; If $sFile is a handle, it will not be closed
; Data is right aligned; set $aiWidth's value to negative to right align data.
; +$aiPrecision uses the absolute value (helpful if $aiPrecision = $aiWidth)
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _Export_FixedWidth($avData, $aiWidth, $aiPrecision, $sFile, $sNewLine = Default)
If IsKeyword($aiPrecision) Then $aiPrecision = $aiWidth
If IsKeyword($sNewLine) Then $sNewLine = @CRLF
; Ensure 2D array
If UBound($avData, 0) <> 2 Then Return SetError(1, 0, False)
; Check sizes of the arrays
If UBound($aiWidth) <> UBound($avData, 2) Then Return SetError(2, 1, False)
If UBound($aiPrecision) <> UBound($avData, 2) Then Return SetError(2, 2, False)
Local $sExportText
; For each row
For $iRow = 0 To UBound($avData) - 1
; For each column
For $iColumn = 0 To UBound($avData, 2) - 1
; Format the data as a string using the width and precision assigned by the user
$sExportText &= StringFormat("%" & (-1 * $aiWidth[$iColumn]) & "." & Abs($aiPrecision[$iColumn]) & "s", $avData[$iRow][$iColumn])
Next
$sExportText &= $sNewLine
Next
If Not FileWrite($sFile, $sExportText) Then Return SetError(3, 0, False)
Return True
EndFunc
As the VBA was looping through each cell, reading it (and the precision), and writing it to file, it was taking upwards of 10 minutes as the worksheet was 72 columns x 15367 rows (~1.1 M cells). I'm very impatient and this was just too much. Now it takes less than a second to write the data I lied, I needed more error checking. 🤦♂️ However, 24 seconds is still much better