MeadInKent
| 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 A button placed on an Excel worksheet with a macro assigned to it  
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 MeadInKent.co.uk 2016 CMIDX S5 P20a Y