| Feedback | Part Two of text files |

Exporting text files from Excel - sharing data with other programs

Excel allows users to save workbooks in a variety of formats including text (TXT), comma separated values (CSV) and XML files. Occasionally however you may either only want to export a particular range of cells or the destination application may not accept the precise file format created by Excel. It can then be useful to export data by creating your own text files.

This section contains various examples of simple macros which will export data to (or import data from) text files.

5     StartRow 8      
6 Charges to Medical staff Items 6 A button placed on an Excel worksheet with a macro assigned to it  
8 DR MOSS 12563 43 THE STREET CANTERBURY 01899 852258 Telephone calls 21/3 14.63
9 PROF FLOWER 1225 28 NORTH ROAD MARGATE 01899 562236 Room hire 14/3-16/3 85
10 MISS DAISY 15523 26 MARGATE DRIVE WHITSTABLE 06223 223556 Damage to desk 122.32
11 DR WEED 1300 GATSBY, DOVER AVENUE PADDOCK WOOD 06223 224514 Overpayment in June 48.6
12 DR BINDING 13330 224 CANTERBURY STOUR CANTERBURY 01688 512555 Telephone calls 24/3  5.23
13 MR GREENACRE 25336 RED FARM, STURRY RD CHARING 01899 852114 Mess bill - extra milk 14.5

Export an Excel table as a fixed width text file

Many program interfaces require a fixed width string of text to import data. Within these strings, different fields are consolidated and each piece of data is placed at a specific position in every record. For example an address field may begin at character 23. This format requires that fields are limited to a maximum number of characters in order not to overlap with the next data item. It is also necessary to provide padding in between items to properly space them.

A regular table of data (such as in the example above) can either be exported directly to a text file or the fixed width strings could be created in another range of cells, ready to be copied or exported as a separate step.

A macro to transfer data into fixed width string in another range of cells

A macro can be linked to a button which exports a table of data to a text file such as the following:

DR MOSS      12563 43 THE STREET        CANTERBURY   01899 852258 Telephone calls 21/3   0000014.63
PROF FLOWER   1225 28 NORTH ROAD        MARGATE      01899 562236 Room hire 14/3-16/3    0000085.00
MISS DAISY   15523 26 MARGATE DRIVE     WHITSTABLE   06223 223556 Damage to desk         0000122.32
DR WEED       1300 GATSBY, DOVER AVENUE PADDOCK WOOD 06223 224514 Overpayment in June    0000048.60
DR BINDING   13330 224 CANTERBURY STOUR CANTERBURY   01688 512555 Telephone calls 24/3   0000005.23
MR GREENACRE 25336 RED FARM, STURRY RD  CHARING      01899 852114 Mess bill - extra milk 0000014.50

This file was produced by the following macro.

Sub MakeFixedWidth()
Dim MyStr As String, PageName As String, FirstRow As Integer, LastRow As Integer, MyRow As Integer

PageName = "C:\TextFileFW" & Format(Time, "HHMM") & ".txt" ' location and name of saved file
FirstRow = Range("D5").Value ' the range of the table to be exported
LastRow = FirstRow + Range("D6").Value - 1

Open PageName For Output As #1
For MyRow = FirstRow To LastRow ' loop through each row of the table
 MyStr = ""
 MyStr = Cells(MyRow, 1).Value & String(15 - Len(Cells(MyRow, 1).Value), " ")
 MyStr = MyStr & String(7 - Len(Cells(MyRow, 2).Value), " ") & Cells(MyRow, 2).Value
 MyStr = MyStr & " " & Cells(MyRow, 3).Value & String(20 - Len(Cells(MyRow, 3).Value), " ")
 MyStr = MyStr & Cells(MyRow, 4).Value & String(15 - Len(Cells(MyRow, 4).Value), " ")
 MyStr = MyStr & Cells(MyRow, 5).Value & String(13 - Len(Cells(MyRow, 5).Value), " ")
 MyStr = MyStr & Cells(MyRow, 6).Value & String(25 - Len(Cells(MyRow, 6).Value), " ")
 MyStr = MyStr & Format(Cells(MyRow, 7).Value, "0000000.00")
Print #1, MyStr
Close #1
Sheets("DATA").Range("G2").ClearContents ' note that this row expects the worksheet to be named DATA
Sheets("DATA").Hyperlinks.Add Range("G2"), PageName
End Sub

See also help in creating macros or assigning macros to buttons.

A formula to create a fixed width string from a range of cells

18   StartRow 21  
19 Compiled data using a formula Items 3
20 ....|....1....|....2....|....3....|....4....|....5....|....6....|....7....|....8....|....9....|....0....|....1.
21 DR MOSS        12563  43 THE STREET      CANTERBURY      01899 852258 Telephone calls 21/3     0000014.63
22 PROF FLOWER     1225  28 NORTH ROAD      MARGATE         01899 562236 Room hire 14/3-16/3      0000085.00
23 MISS DAISY     15523  26 MARGATE DRIVE   WHITSTABLE      06223 223556 Damage to desk           0000122.32

The formula (in cell A21) to create this string is as follows:

=A8 & REPT(" ",15-LEN(A8)) & B8 & REPT(" ",7-LEN(B8)) & C8 & REPT(" ",20-LEN(C8)) & D8 & REPT(" ",15-LEN(D8)) & E8 & REPT(" ",13-LEN(E8)) & F8 & REPT(" ",25-LEN(F8)) & TEXT(G8,"0000000.00")

The REPT() function is used to pad the spaces in between each of the data items. It adds a number of spaces, dependent upon the length of the text value in each cell. Instead of padding the final numeric value, the TEXT() function has been used to convert its format.

A formula to create a single string containing comma separated values from a range of cells

26 "DR WEED",1300,"GATSBY, DOVER AVENUE","PADDOCK WOOD","06223 224514","Overpayment in June",48.6
27 "DR BINDING",13330,"224 CANTERBURY STOUR","CANTERBURY","01688 512555","Telephone calls 24/3",5.23
28 "MR GREENACRE",25336,"RED FARM, STURRY RD","CHARING","01899 852114","Mess bill - extra milk",14.5

The formula (in cell A26) to create this string is as follows:

=CHAR(34) & A11 & CHAR(34) & "," & B11& "," & CHAR(34) & C11& CHAR(34) & "," &CHAR(34) & D11& CHAR(34) & "," & CHAR(34) & E11& CHAR(34) & "," & CHAR(34) & F11& CHAR(34) & "," & G11

Note that the function CHAR(34) inserts speech marks ("). Numeric fields are not normally enclosed with speech marks.

It is helpful to format cells containing fixed width strings with a font such as Courier which spaces each character evenly. Otherwise a True Type font (such as Ariel) will shrink the padded spaces and obscure the formatting.

There may be little call for producing a comma separated field with this long manual method. Excel will save an identical text file simply by supplying each of the fields as columns or fields on a form (see next example). There may however be occasions when fussy programs expect CSV files to be in a slightly different format - such as with no speech marks around the text.

Part two - exporting data from a form

This page is included in
the Excel functions guide
Click here for details about
obtaining this file

file: xlexport-text1.htm.htm Last updated Aug06 2016 CMIDX S5 P20 Y