
Home	Excel	Access
\| 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) and comma separated values (CSV). Some modern applications are also able to exchange data in XML files and programs can be written to export Excel data in this format. Occasionally however you may either only want to export a particular range of cells or the destination application may not accept the XML format. It can then be useful to export specific ranges of data from a worksheet into a text file.

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

Export a table of data to fixed width text file
Create fixed width text strings from a table of data
Create comma separated strings from a table of data in a separate range of cells
Send data from a form to a table in another range of cells
Export data from a form to a comma separated text file with appropriate data types

	A	B	C	D	E	F	G
5			StartRow	8
6	Charges to Medical staff		Items	6
7	NAME	ACCOUNT	ADDRESS 1	ADDRESS 2	PHONE	DESCRIPTION 1	AMOUNT
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

Would you like to learn more?

Business Analysis with Microsoft Excel

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
Next
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

	A	B	C	D	E
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

	A	B	C	D	E
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 separate parameters (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 not currently included
in the Excel functions guide

Click here for details about
obtaining this file

file: xlexport-text1.htm.htm

Last updated Aug06

View a selection of recommended books on Excel