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

[Marc L]

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 ‼ ​

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]

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
    Next
         Close #FF
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

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
    Next
        Close #FF
End Sub

You could Like it !​

​

 

[Marc L]

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)
    Next
        Close #F
End Sub

You may Like it !​

​

 

[Marc L]

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));
        Next
            Print #F,
    Next
            Close #F
End Sub

You should Like it !
​

 

[Marc L]

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));
        Next
            Print #F,
    Next
            Close #F
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

Bonus​

as a

!​

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
        Next
             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);
            Next
                Print #F, Tab(L)
        Next
                Close #F
    End With
End Sub

You like this code ? So click on bottom right Like ! Thanks !​

​

 

[Marc L]

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 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 …​

 

[Marc L]

A variation of the VBA procedure ExportFixedLength2n for specific right formats in this thread :​

Help, How to set Right and Left function for different columns??

>>> use code - tags

chandoo.org

 

[Hui]

Marc

Nice Job !

Would you like that written up as a Blog Post ?

.

 

[Marc L]

Hi Hui,​

​

thanks ! Two years later but yes why not !​

​

 

[Hui]

I'll do it over the weekend and then send you a link to review before publishing

 

[Marc L]

Ok, do it your way when you have some free time, thanks !​

​