• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Two Old School Ways To Export Cells To A Fixed Length Text File

Not open for further replies.

Marc L

Excel Ninja
Yes I know : it's not fancy but I saw from time to time always the same subject : how to …​
Yes I know : many samples on web whatever in forums or blogs, so I thought of some lazy people requering to do their job !​
But recently I saw a blog on this subject, a nice looking webpage but so ugly is the code ‼ :eek:
I was very surprised some VBA basics was not used as it's easier for beginners.​
So I searched on web and all I found was often long codes with useless stuff always trying to fill holes adding spaces​
but never using VBA basics inherited in fact from its grand pa' : the BASIC ‼​
-- ★ --
First well think about your export text file destination as Excel already has an easy feature : Save As !​
If the receiver of this text file can warm a couple of neurons to fit an import procedure in its database for example​
then you do not ever need any VBA procedure !​

  • First enlarge or reduce the columns widths to well fit the fixed length text format.
  • In the File menu select Save As then from the File Type drop-down choose Formatted Text (Space delimited) (*.prn)
After creating the .prn text file you can easily rename it if needed as .txt for example.​
And if you really need a macro code, see the Worksheets.SaveAs method in VBA help or for a starter just​
activate the Macro Recorder before operating manually : you will get your own free code, at beginner level …​
(You can first copy the sheet to a new workbook, see Worksheets.Copy in VBA help.)
No need to continue reading this thread anymore ! Yes I know : I make your day, bye !​

-- ★ ★ --​
You are still here ‼​
So that's the second case : the database expects only a specific fixed format the receiver already gave you.​
And as you do not succeed with the previous Save As Excel easy feature then you - maybe - need​
a VBA code to export data to a fixed length text file : just keep reading this thread !​

-- ★ ★ ★ --
Next posts consist on several demonstrations for two basics ways, from very beginner level​
to beginner / advanced level and a bonus demonstration as a 'cherry on the cake' code,​
the final post for additional information and a workbook attachment with all demonstrations.​
Learning by examples and by reading as it's only VBA basics, all is already in VBA help !​
As often I merely can't better explain than VBA help so before any question just well read it …​
You try but you fail : do not create any post here but as per this forum rules (in particular How to get the Best Results)
just create your own thread in the appropriate VBA Macros section.​

Marc L

Excel Ninja
Way #1 (at very beginner level)
Let's start with the context : an unique sheet workbook with data headers starting in cell A1,​
five columns to export to a fixed length text file respecting this structure :​
Column . . . . :. A . .B . .C . .D . .E
Maximum length :. 30 . 25 . 20 . 15 . 10
An easy way is to start to declare a fixed length String variable for each column​
as for example for A & B columns Dim A As String * 30, B As String * 25 and so on …​
The Text property of each cell of a row is allocated to its respective String variable​
then these variables are written to the text file on a same line, that's all folks !​
Let's see this first easy demonstration where the text file is created in the folder workbook​
and, as already explained, all is in VBA help :​
Sub ExportFixedLength1()
    Dim A As String * 30, B As String * 25, C As String * 20, D As String * 15, E As String * 10
    Dim FF%, Rg As Range
        FF = FreeFile
        Open ThisWorkbook.Path & "\ExportFL 1 .txt" For Output As #FF
    For Each Rg In ActiveSheet.UsedRange.Rows
         A = Rg.Cells(1).Text
         B = Rg.Cells(2).Text
         C = Rg.Cells(3).Text
         D = Rg.Cells(4).Text
         E = Rg.Cells(5).Text
         Print #FF, A; B; C; D; E
         Close #FF
End Sub
Do you like it ? So thanks to click on bottom right Like !​

Marc L

Excel Ninja
Previous demonstration well works as it is but if you need to add some columns you must modify​
  • the Dim codeline for the fixed length String variables so this codeline may become a bit long
  • the data allocation within the loop
  • the Print # codeline writing the variables contents to the text file …
For clarity instead of using a Dim codeline for the fixed length String variables inside the procedure​
better is to declare a user-defined data type at the module level - Type block statement - like a database record.​
It must contain the same fixed length String variables and another one (Z in the below demonstration)
for the end of line characters sequence aka CarriageReturn & LineFeed (chars code #13 & #10) under Windows.​
The procedure just needs to declare a single variable as this record type​
and within the loop the data allocation uses the inner variables of this single variable data record.​
The benefit is the Put # codeline writing the data record to the text file never changes !​
My revamped demonstration to paste to the top of a module :​
Type REC
    A As String * 30
    B As String * 25
    C As String * 20
    D As String * 15
    E As String * 10
    Z As String * 2
End Type

Sub ExportFixedLength1r()
    Dim S As REC, F$, FF%, Rg As Range
        S.Z = vbCrLf
        F = ThisWorkbook.Path & "\ExportFL 1r .txt"
        If Dir(F) > "" Then Kill F
        FF = FreeFile
        Open F For Binary As #FF
    For Each Rg In ActiveSheet.UsedRange.Rows
       S.A = Rg.Cells(1).Text
       S.B = Rg.Cells(2).Text
       S.C = Rg.Cells(3).Text
       S.D = Rg.Cells(4).Text
       S.E = Rg.Cells(5).Text
        Put #FF, , S
        Close #FF
End Sub
You could Like it !​

Marc L

Excel Ninja
Way #2 (easy logic coming yet from VBA help)
Instead of directly sharing the final code let's first see two intermediate procedures​
in order beginners may better follow and understand the child logic of this way …​
As it does not need fo fill holes adding spaces neither to use any fixed length String variable,​
if reading is a competence as I have already seen children succeed in planning an easy logic​
just after reading the help of the Print # statement, as again all is in VBA help​
so just take a moment to open and well read this help …​
(After a while) So you see ? (If no so this time just well read again !)
Yes, with its Tab argument Print # directly writes to an absolute position​
so the spaces are added automatically when necessary !​
That just needs to calculate the starting position of each column, according to the post #2 initial context,​
as the maximum length of the first column is 30 so the second column starting position is 31 (1+30),​
as the maximum length of the second column is 25 so the third column starts at position #56 (31+25), and so on …​
As the starting position of the last column is 91 and its maximum length is 10, so the end of line starts at #101​
(sum of lengths + 1).​
This is the shortest demonstration :​
Sub ExportFixedLength2ia()
    Dim F%, Rg As Range
        F = FreeFile
        Open ThisWorkbook.Path & "\ExportFL 2ia .txt" For Output As #F
    For Each Rg In ActiveSheet.UsedRange.Rows
        Print #F, Rg.Cells(1).Text; Tab(31); Rg.Cells(2).Text; Tab(56); Rg.Cells(3).Text; Tab(76); Rg.Cells(4).Text; Tab(91); Rg.Cells(5).Text; Tab(101)
        Close #F
End Sub
You may Like it !​

Marc L

Excel Ninja
You have certainly noticed how long is the Print # codeline in the previous demonstration.​
Of course I could split it in two lines but imagine the code for twenty columns or more …​
Combining a loop with an array for each column writing position simplifies the code​
- if more columns are necessary the Print # codeline never changes, just needs an array mod -​
like in this new intermediate procedure :​
Sub ExportFixedLength2ib()
        Dim F%, V, Rg As Range, C%
            F = FreeFile
            V = [{31,56,76,91,101}]
            Open ThisWorkbook.Path & "\ExportFL 2ib .txt" For Output As #F
    For Each Rg In ActiveSheet.UsedRange.Rows
        For C = 1 To UBound(V)
            Print #F, Rg.Cells(C).Text; Tab(V(C));
            Print #F,
            Close #F
End Sub
You should Like it !

Marc L

Excel Ninja
Way #2 (The One !)
Both previous intermediate procedures well work only if no cell exceeds the length limit​
as the drawback of Print # statement is it first writes all the cell content and​
if the next column position is yet occupied it writes the next data into a new line !​
To get the same result as the first way it just needs for example the Left function​
in order to never exceed the length limit and starting this time from a length array​
the procedure can easily calculate each column writing position in another array :​
Sub ExportFixedLength2()
        Dim V, P%(), C%, F%, Rg As Range
            V = [{30,25,20,15,10}]
            ReDim P(1 To UBound(V))
            P(1) = V(1) + 1
            For C = 2 To UBound(V):  P(C) = P(C - 1) + V(C):  Next
            F = FreeFile
            Open ThisWorkbook.Path & "\ExportFL 2 .txt" For Output As #F
    For Each Rg In ActiveSheet.UsedRange.Rows
        For C = 1 To UBound(V)
            Print #F, Left(Rg.Cells(C).Text, V(C)); Tab(P(C));
            Print #F,
            Close #F
End Sub
Do you like it ? So thanks to click on bottom right Like !​

Marc L

Excel Ninja
as a
cherry on a cake

Previous demonstrations use hardcoded export structure but to avoid to modify the code​
when adding / removing a column or even just reversing the order of two columns​
better is to store the columns maximum lengths at the worksheet level …​
Those who regularly export different data structures from the same data source​
can make the choice of an additional Settings worksheet.​
When the need is occasional an easy way is to just add a numeric comment on each column header.​
For example when moving a column before a previous one, you must modify the Settings worksheet​
but with numeric comments no mod is necessary as the comments follow their headers !​
Instead of a two dimensions array like usually advanced / expert VBA coders use, for clarity for beginners​
this bonus demonstration stores the export characteristics (column index, maximum length and writing position)
in an user-defined data type allocated from the headers comments.​
Code to paste to a new module :​
Type CLP
     Col As Integer
     Lng As Integer
     Pos As Integer
End Type

Sub ExportFixedLength3()
            Dim L%, E() As CLP, Ct As Comment, N%, F%, Rg As Range, C%
                L = 1
    With ActiveSheet.UsedRange
                ReDim E(1 To .Columns.Count)
        For Each Ct In .Parent.Comments
              If Ct.Parent.Row = 1 And IsNumeric(Ct.Text) Then
                N = N + 1
                E(N).Col = Ct.Parent.Column
                E(N).Lng = Ct.Text
                E(N).Pos = L
                L = L + E(N).Lng
              End If
             If N = 0 Then MsgBox "No header with a length comment", vbExclamation, "Export fixed length": Exit Sub
                F = FreeFile
                Open ThisWorkbook.Path & "\ExportFL 3 .txt" For Output As #F
        For Each Rg In .Rows
            For C = 1 To N
                Print #F, Tab(E(C).Pos); Left(Rg.Cells(E(C).Col).Text, E(C).Lng);
                Print #F, Tab(L)
                Close #F
    End With
End Sub
You like this code ? So click on bottom right Like ! Thanks !​

Marc L

Excel Ninja
What is the best code ?​
As always, the one you understand and you are able to maintain yourself ! …​

-- ★ --
As it's nothing but text I can't understand why numeric values should be right formatted​
(like the prn file from Save As) except for a visual check but for an import it's totally useless !​
After many years and dozens of text files, I never met any issue for left formatted numeric values, the easy way …​
But if a dumb receiver / database really requires such a format :rolleyes: this is the mod to apply for the​

  • Way #1 : it needs an additional sub procedure to paste in the same module :
Sub Allocate(Rg As Range, S$)
    Dim T$
        T = Rg(1).Text
        If IsNumeric(T) Then T = Space(Len(S) - Len(T)) & T
        S = T
End Sub
For example if column E needs to be right formatted if any numeric value​
so replace in first demonstration the original codeline E = Rg.Cells(5).Text by Allocate Rg.Cells(5), E …​

  • Way #2 ExportFixedLength2 demonstration :
    it needs to be rewritted the same way as ExportFixedLength3 procedure, see ExportFixedLength2n in the below attachment.

  • cherry on the cake bonus ExportFixedLength3 :
    it needs an additional String variable called S in order to apply an offset to the position
    then the original codeline within the loop Print #F, Tab(E(C).Pos); Left(Rg.Cells(E(C).Col).Text, E(C).Lng);
    must be replaced by these two codelines :
    S = Left(Rg.Cells(E(C).Col).Text, E(C).Lng)
    Print #F, Tab(E(C).Pos - (E(C).Lng - Len(S)) * IsNumeric(S)); S;
In the attachment D3 cell has a numeric value, compare the text files between ExportFixedLength2 & ExportFixedLength2n for example …​

-- ★ ★ --
In the workbook attachment below containing all the demonstrations :​

  • D2 cell oversizes its column maximum length, see the funny weird result text file
    from any of the way #2 intermediate procedures (ExportFixedLength2ia or 2ib).

  • Each header has a comment but if you inspect the result text file of the cherry on the cake last demonstration
    you must notice column D is skipped : its comment is non numeric, like when the header has no comment …

  • Enter a length in the column D header comment (try 11 or 15), move this column after column E
    then execute ExportFixedLength3 and see its new result text file : too easy, without any mod in code !
Edit 8/8/2019 : new attachment version of ExportFixedLength2n
for OS with a comma as decimal separator to export numeric values with a dot instead …



Excel Ninja
Staff member
I'll do it over the weekend and then send you a link to review before publishing
Not open for further replies.