Text File Layout

Hi ALL

I am writing data from an access db to a text file and then printing it using a receipt printer, this all works well, but was wandering how to get the layout looking nicer for example the bill prints out as follows.

1 X Burger and chips R27.00
1 X pizza R30.00

Total = R57.00

is there a way to print it out like this maybe.

1 X Burger and Chips R27.00
1 X Pizza R30.00

Total = R57.00

and then put a line under the total.

Here is the code i use to write to the text file.

Dim FileLength
Dim ff As Integer
ff = FreeFile()
Open ("C:\Pos\PrintBill.txt") For Append As ff
If FileLength = LOF(1) Then
While Not MyRecSet.EOF
Print #ff, MyRecSet.Fields("Qty"); MyRecSet.Fields("Description"); " "; "R"; MyRecSet.Fields("Price")
MyRecSet.MoveNext
Wend

Many thanks

You can print a string of overline symbols under the last entry. If you count the number of characters in the total, use this length to make the string. It will be approximatly the length of the total string unless you use a fixed width font.

Dim sTotal As String
Dim sItems As String

'Get the items from the database.
sItems = "1 X Burger and Chips R27.00" & vbCrLf & "1 X Pizza R30.00"
'Get the total.
sTotal = "Total = R57.00"
'Print the data.
Debug.Print sItems
Debug.Print
Debug.Print sTotal
'Underline the total (ASCII #175 is a upper character line).
Debug.Print String$(Len(sTotal), Chr$(175))

I assume you want the Numbers to line up in a column.
Use the PosTab function to specfiy what column you want them in, i.e.

Print #ff, MyRecSet.Fields("Qty"); MyRecSet.Fields("Description"); " "; "R"; Tab(40); MyRecSet.Fields("Price")
Print #ff, "Total ="; Tab(40); sTotal.

You might also want to use Format$ if you want the decimal points to line up in the price column.

Hi Passel

Thank you, i am trying your suggestion out as i would like to get the "Price"
in a column. Just a question, what do i declare the Pos(40) as, i have tried
integer and string but i get an Expected array error, and also where do i
put the Format$ to get the decimal points in the price.

Many thanks for your help

hi

I have sorted out the format problem, but i am still struggling to position all the prices underneath each other, My first post looks different to the way i typed it, i am trying to get the prices lined up something like this.

R12.50
R25.00

Many thanks

haha yeah, you're first post wasnt alot of help formatting-wise :)

you could try putting a vbTab character in your output string

also, as far a a line, is it possible the printer device has any commands to produce things like that?

Note to self... really, really check your code.
I think Pos and Tab were available in QBasic, I'll have to check it.
But, in this case, for VB I really meant use the Tab function to put the cursor in a specific column, so replace Pos with Tab in the code I gave.
New example.

Option Explicit

Private Function PriceFormat(amt As Double) As String
PriceFormat = Format$(amt, "0.00")
End Function

Private Sub Command1_Click()
Open "c:\out.txt" For Output As #1
Print #1, "Test 1"; Tab(40); Format$(PriceFormat(12.34), "@@@@@@@")
Print #1, "Second Test 1"; Tab(40); Format$(PriceFormat(125.34), "@@@@@@@")
Print #1, "Third Test 1"; Tab(40); Format$(PriceFormat(1125.34), "@@@@@@@")
Close #1
End Sub

Hi Passel

Thank you, the tab(40) gives the space that i would like from the description, but the only problem is, is that the "Descriptions" vary in length so the price comes out like this.

1 x bacon and cheese...........R23.50
1 x Burger...........R20.00

instead of the prices being in a column underneath each other.

PS. i just put the dots in for posting purposes to get the spaceing correct.

Many thanks

Instead of using a fixed tab amount, use Tab(40 - len(your description field))

Hi Xamonas

i tried what you have suggested.

While Not MyRecSet.EOF
Print #ff, MyRecSet.Fields("Qty"); Tab(40 - Len(MyRecSet.Fields("Description"))); " "; "R"; MyRecSet.Fields("Price")
MyRecSet.MoveNext
Wend

But then the description does not print.

Many thanks

...because you are tabbing but not printing the field! Try this:

Print #ff, MyRecSet.Fields("Qty"); MyRecSet.Fields("Description"); Tab(40 - Len(MyRecSet.Fields("Description"))); " "; "R"; MyRecSet.Fields("Price")

Hi Xamonas

Thank you, for your help, it is working much better for most of the Descriptions but some that are very short like Pizza and ones that are long, the prices still don't line up. Is there anything else we could try.

Many thanks

The Tab function shouldn't add a fixed number of spaces. It should position the cursor to the logical 40th column (or whatever column you specify). The preceeding length of the text shouldn't matter unless the cursor is already past the 40th position, in which case it should go to the 40th position on the next line. The Spc function adds a fixed number of spaces after the proceeding text.
I will have to look at it again after I get back from an appointment.
The second example I gave worked fine when I looked at the file in Notepad, but of course Notepad uses a Fixed Font. I will try to see if other conditions, or using WordPad or Word, I can get mis-alignment.

You are quite right passel, I was thinking of Spc!

In that case AJ, I think you just need a larger Tab value. Some of your descriptions appear to take the print position beyond 40 before you issue the tab command. Make sure that your tab value is at least 1 more than the combined length of your longest Qty & Description fields.

hi Guys

Thank you, my understanding of writing to text files is rather limited, i have tried increasing and decreasing the tab value, which just increases the space between the description and the price, but the price does'nt want to allign, i am writing to a standard .txt file. Do i have to Declare the Tab?.

Many thanks

How are you looking at the Text file.
For instance, I modified the code given in example 2 as follows.

Option Explicit

Private Function PriceFormat(amt As Double) As String
PriceFormat = Format$(amt, "0.00")
End Function

Private Sub Command1_Click()
Dim a As String, i As Long

a = String(35, "a")
Open "c:\out.txt" For Output As #1
For i = 1 To 35
Print #1, Left$(a, i); Tab(40); Format$(PriceFormat(12.34), "@@@@@@@")
Next
Close #1
End Sub

The output of the file, in notepad or wordpad, looks like this.

a 12.34
aa 12.34
aaa 12.34
aaaa 12.34
aaaaa 12.34
aaaaaa 12.34
aaaaaaa 12.34
aaaaaaaa 12.34
aaaaaaaaa 12.34
aaaaaaaaaa 12.34
aaaaaaaaaaa 12.34
aaaaaaaaaaaa 12.34
aaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 12.34


The earlier example's (example 2) output looked like this in wordpad or notepad.

Test 1 12.34
Second Test 1 125.34
Third Test 1 1125.34

hi Passel

Thank you for your reply, i can't seem to figure out what i am doing differently, below is my code to write all the data to the text file, and then print it out, maybe there is something in there that i am doing wrong.

Private Sub cmdPrintBill_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim sSQL As String
Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pos\pos.mdb;"
MyConn.Open
sSQL = "SELECT Qty, Description, Price FROM Sales1 WHERE Tbl_No = '" & lblTabNo.Caption & "'"
MyRecSet.Open sSQL, MyConn, adOpenStatic, adLockReadOnly


Dim FileLength
Dim ff As Integer
ff = FreeFile()
Open ("C:\Pos\PrintBill.txt") For Append As ff
If FileLength = LOF(1) Then
Print #ff, " "; frmPrintDetails.txtCompName.Text
Print #ff, "---------------------------------------------------- "
Print #ff, frmPrintDetails.lblVatNo; frmPrintDetails.txtVatNo.Text
Print #ff, frmPrintDetails.lblTel; frmPrintDetails.txtTel.Text
Print #ff, frmPrintDetails.lblFax; frmPrintDetails.txtFax.Text
Print #ff, frmPrintDetails.lblMail; frmPrintDetails.txtMail.Text
Print #ff, "###############################"
Print #ff, " PRO FORMA ONLY"
Print #ff, "###############################"
Print #ff, " "
End If

While Not MyRecSet.EOF
Print #ff, MyRecSet.Fields("Qty"); MyRecSet.Fields("Description"); Tab(40 - Len(MyRecSet.Fields("Description"))); "R"; MyRecSet.Fields("Price")
MyRecSet.MoveNext
Wend
Print #ff, " "
Print #ff, Label1.Caption; lblTotal.Caption
Print #ff, " "
Print #ff, "###############################"
Print #ff, frmPrintDetails.txtComment.Text
Printer.EndDoc
Close
Unload frmPrintDetails
End Sub


Private Sub cmdPrintBill_MouseUp(Button As Integer, Shift As Integer, x As Single, y As Single)
Screen.MousePointer = vbHourglass
Printer.FontSize = 14
Printer.Print PrintTextFile("C:\Pos\PrintBill.txt")
Printer.EndDoc
Screen.MousePointer = vbDefault

Dim ff As Integer
ff = FreeFile()
Open ("C:\Pos\PrintBill.txt") For Output As ff
Close
End Sub

Many thanks, for all your help.

Well, you shouldn't need the 40 - .... (don't need to subtract len from the col)
Tab(Col) should specify which column you want the field to be printed in.
The problem is that you need a fixed spaced font.
Add
Printer.Fontname = "Courier New"
near Printer.FontSize and give that a try.

You don't really need to put this stuff in a file, since you clear the file write after you print it.
Everywhere you have Print #ff you could just have Printer.Print and print the receipt as you go.
That would also give you the option of putting the "fields" exactly where you want by specifying Printer.CurrentX (and Printer.CurrentY if necessary).

For now, try changing the printer's font and see if that doesn't straighten things up.

I'm going to emphasize something here, passel.

Alexander: Most type faces are proportional. This means that an "M" or a "W" use a lot more horizontal space than does a space or an i. However, this also means that it is hard to align data with them, unless you can calculate exactly how much space a printed string will require, and set your print position to accomodate this. Windows print drivers allow this, but, text printers do not.

So, you have to resort to the older approach of padding with spaces, AND using a NON-propportional font like Courier.