
Home	Excel	Access
\| Feedback \| Part One of text files \| Forms \|

Exporting text files from Excel - data from a form

[Back to page one]

The previous page has looked at saving a table or list of data in a fixed width format. The second example takes a single record from a form and saves it in a text file with distinct fields for each piece of data. The details will be saved in a comma separated file which can easily be recognised and imported by other programs without any complicated decyphering. This method could have been applied to the previous table.

Would you like to learn more?

Excel 2010 Bible by John Walkenbach (with CD)

	B	C	D	E
6	Simple Debtors Invoice
7
8	Customer	Tony Tinker
9	Invoice number	14558
10	Raised by	Chris
11
12		Description	Amount
13	Item 1	Wag the Dog DVD	21.55
14	Item 2	Carry on Doctor	16.50
15	Invoice total		38.05
16

The data from the form could be posted into a table contained in a range elsewhere in the spreadsheet (click here for an example).

If the data is to be exported to a text file the program will need to determine whether it posting the record to a new text file or if it is adding data to an already existing file. The <Run Macro> button on the form (above) is linked to the following macro.

Sub MakeInv()
Dim CustName As String, CustNo As String, Raised As String, MyFileName As String
Dim Item1Desc As String, Item1Amt As Single, Item2Desc As String, Item2Amt As Single

CustName = Range("C8").Value
CustNo = Range("C9").Value
Raised = Range("C10").Value
Item1Desc = Range("C13").Value
Item1Amt = Range("D13").Value
Item2Desc = Range("C14").Value
Item2Amt = Range("D14").Value
MyFileName = "C:\formtest.txt"

If DoesFileExist(MyFileName) = 0 Then
Open MyFileName For Output As #1 ' create a new file and record if file does not exist
Else
Open MyFileName For Append As #1 ' append a record if file does already exist
End If

Write #1, CustName, CustNo, Raised, Item1Desc, Item1Amt, Item2Desc, Item2Amt
Close #1

MsgBox MyFileName & " exported", vbOKOnly, "MeadInKent"
End Sub

Function DoesFileExist(FileName As String) As Byte
' returns 1 if FileName exists, otherwise 0
If Dir(FileName, vbNormal) <> "" Then
DoesFileExist = 1
Else
DoesFileExist = 0
End If
End Function

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

The exported text file (named C:\formtest.txt) looks like this:

"Tony Tinker","14558","Chris","Wag the Dog DVD",21.55,"Carry on Bob",16.5
"Paul Sailor","14552","Chris","Wachet Betet",15.99,"Beati Quorum",4.2
"Bob Tailor","14553","Chris","Smelling salts",1.23,"Copper wire",45

This macro can be modified to import the contents of a text file into a range of cells.

Sub GetInvs()
Dim CustName As String, CustNo As String, Raised As String, MyFileName As String, RowNo As Integer
Dim Item1Desc As String, Item1Amt As Variant, Item2Desc As String, Item2Amt As Variant

RowNo = 20 ' the worksheet row at which to start importing
MyFileName = "D:\formtest.txt"

Cells(RowNo - 1, 1).Value = "File '" & MyFileName & "' loaded at " & Format(Now, "hh:mm dd-mmm-yy")
Open MyFileName For Input As #2
Do While Not EOF(2)
Input #2, CustName, CustNo, Raised, Item1Desc, Item1Amt, Item2Desc, Item2Amt
Cells(RowNo, 1).Value = CustName
Cells(RowNo, 2).Value = CustNo
Cells(RowNo, 3).Value = Raised
Cells(RowNo, 4).Value = Item1Desc
Cells(RowNo, 5).Value = Item1Amt
Cells(RowNo, 6).Value = Item2Desc
Cells(RowNo, 7).Value = Item2Amt
RowNo = RowNo + 1
Loop

Close #2
End Sub

	A	B	C	D	E	F	G
19	Import text file of invoices
20	Tony Tinker	14558	Chris	Wag the Dog DVD	21.55	Carry on Bob	16.5
21	Paul Sailor	14552	Chris	Wachet Betet	15.99	Beati Quorum	4.2
22	Bob Tailor	14553	Chris	Smelling salts	1.23	Copper wire	45

Part one - exporting data from a list

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 Nov11

View a selection of recommended books on Excel