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.

	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 included in
the Excel functions guide

Click here for details about
obtaining this file

file: xlexport-text1.htm.htm

Last updated Nov11

CMIDX S5 P20a Y