|| Feedback | Part One of text files | Forms ||
[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)
|6||Simple Debtors Invoice|
|13||Item 1||Wag the Dog DVD||21.55|
|14||Item 2||Carry on Doctor||16.50|
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.
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
Open MyFileName For Append As #1 ' append a record if file does already exist
Write #1, CustName, CustNo, Raised, Item1Desc, Item1Amt, Item2Desc, Item2Amt
MsgBox MyFileName & " exported", vbOKOnly, "MeadInKent"
Function DoesFileExist(FileName As String) As Byte
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.
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
|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||© MeadInKent.co.uk 2011|