Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

how to make header and footer on text file??

Status
Not open for further replies.
fule12

fule12

Programmer
Nov 12, 2001
140
YU
Hi to All
If sameone cant help me with exporting form to text file.
I make module :
Private Sub cmbExport_Click()
Dim db As Database, rst As Recordset, SQL As String
Dim a As Integer, OrderNo, Vessel, AccCode, RDate, Cost, Myfile As String
Set db = CurrentDb
Myfile = "C:\_Nikola\test.txt"
SQL = "SELECT Order.OrderNo, AccCode.Code, Vessel.VesselCode, Order.Date, Order.EstCostUSD" & _
" FROM Vessel INNER JOIN (AccCode INNER JOIN [Order] ON AccCode.AccCodeID = Order.AccCodeID) ON Vessel.VesselID = Order.VesselID" & _
" WHERE (((Order.EstCostUSD) Is Not Null))" & _
" ORDER BY Vessel.VesselCode"
Set rst = db.OpenRecordset(SQL)
rst.MoveLast
rst.MoveFirst
Open Myfile For Output As #1
    For a = 1 To rst.RecordCount
    OrderNo = Trim(rst!OrderNo) & Space(25 - Len(Trim(rst!OrderNo)))
    AccCode = Trim(rst!Code) & Space(8 - Len(Trim(rst!Code)))
    Vessel = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))
    RDate = Trim(rst!Date) & Space(10 - Len(Trim(rst!Date)))
    Cost = Trim(rst!EstCostUSD) & Space(15 - Len(Trim(rst!EstCostUSD)))
    Print #1, OrderNo & Vessel & RDate & Cost
    rst.MoveNext
    Next
    Close #1
    rst.Close
    db.Close
End Sub
and is work OK but i nead to things:
1.how to make header and footer on text file?
2.how to make text file to be for all Vessel or for only 1 vessel
Thanks
Fule Fule
 
Sort by date Sort by votes
Hi,

When you say you want a header & footer in your text file, do you mean that when your text file prints at, for instance, 66 lines per page, you want the top 4 lines, for instance, to be header and the bottom 3 lines for instance, to be footer?

Or do you mean something else?

Enquiring minds need to know :cool: Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
hi skip,Thanks for re-play
Yes I need on top of page one line what is gona be header.Or if is easy how to export two or tree query in one text file where
one query is gona be header ,secend body and
third footer? Fule
 
Upvote 0 Downvote
fule12,

Well you have to write a procedure that writes a header & footer every x lines based on the lines per page. There is no magic. Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Hi Skip,
Do you now code?
Bicouse i'm rooki in VBA and Access?
Thanks Fule
 
Upvote 0 Downvote
hi,
Here's a way to do it, using some of your code.
Code: 
    Open Myfile For Output As #1
    
    Dim bHeaderFooter As Boolean, sHeader(2), sFooter(2), iPageCount As Integer
    Const LINES_PER_PAGE = 50
    
    bHeaderFooter = True
    iPageCount = 0
    sHeader(0) = "this is my header"
    sHeader(1) = ""                     'blank line for effect
    sFooter(0) = ""                     'blank line for effect
    sFooter(1) = "this is my footer"
    
    For a = 1 To rst.RecordCount
        OrderNo = Trim(rst!OrderNo) & Space(25 - Len(Trim(rst!OrderNo)))
        AccCode = Trim(rst!Code) & Space(8 - Len(Trim(rst!Code)))
        Vessel = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))
        RDate = Trim(rst!Date) & Space(10 - Len(Trim(rst!Date)))
        Cost = Trim(rst!EstCostUSD) & Space(15 - Len(Trim(rst!EstCostUSD)))
        If bHeaderFooter Then
            If a > 1 Then           'output a footer
                For j = 0 To UBound(sFooter, 1)
                    Print #1, sFooter(j)
                Next j
            End If
        End If
        Print #1, OrderNo & Vessel & RDate & Cost
        If bHeaderFooter Then       'output a header
            For j = 0 To UBound(sFooter, 1)
                Print #1, sHeader(j)
            Next j
            bHeaderFooter = False
            iPageCount = 0
        End If
        rst.MoveNext
        iPageCount = iPageCount + 1
        If iPageCount = LINES_PER_PAGE Then
            bHeaderFooter = True
        End If
    Next
    'advance to bottom of page
    For a = 1 To LINES_PER_PAGE - iPageCount
        Print #1, ""
    Next
    'final footer
    For j = 0 To UBound(sFooter, 1)
        Print #1, sFooter(j)
    Next j
    Close #1
Hope this helps :) Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Hi Skip,
Thanks for code,but my header and footer need to start when Vessel Code is change like this:
this is my header
AFR-D6/02 AFR 25/01/02 269.00
AFR-DFM2/02 AFR 31/01/02 2,500.00
AFR-E9b/02 AFR 31/01/02 0.00
AFR-D5/02 AFR 25/01/02 2,570.00
AFR-E24/02 AFR 21/02/02 0.00
AFR-E10/02 AFR 28/01/02 29,140.00
AFR-DFM9/02 AFR 14/03/02 0.00
AFR-DFM7/02 AFR 21/02/02 0.00
AFR-D7/02 AFR 29/01/02 0.00
AFR-E17/02 AFR 08/02/02 367.00
AFR-E25/02 AFR 21/02/02 0.00
AFR-E14/02 AFR 24/01/02 365.00
AFR-E13/02 AFR 24/01/02 0.00
AFR-E12/02 AFR 24/01/02 0.00
AFR-E11/02 AFR 24/01/02 306.00
AFR-DFM6/02 AFR 08/02/02 500.00
AFR-E27/02 AFR 05/02/02 0.00
this is my footer
this is my header
ALC-D49/02 ALC 25/02/02 1,210.00
ALC-E27/02 ALC 23/01/02 500.00
ALC-D44/02 ALC 14/02/02 0.00
ALC-D13/02 ALC 22/01/02 609.00
ALC-D16/02 ALC 22/01/02 251.00
ALC-D46/02 ALC 14/02/02 240.00
ALC-D47/02 ALC 18/02/02 977.00
ALC-E25/02 ALC 22/01/02 506.00
ALC-D15/02 ALC 22/01/02 0.00
ALC-D50/02 ALC 25/02/02 450.00
ALC-E20/02 ALC 22/01/02 3,680.00
ALC-DFM24/02 ALC 14/02/02 2,734.00
ALC-E15/02 ALC 22/01/02 398.00
ALC-D41/02 ALC 23/01/02 1,780.00
ALC-DFM18/02 ALC 28/01/02 7,375.00
ALC-E45/02 ALC 08/02/02 821.00
ALC-DFM17/02 ALC 28/01/02 7,375.00
ALC-E46/02 ALC 13/02/02 0.00
ALC-DFM33/02 ALC 13/03/02 1,558.00
ALC-E39/02 ALC 01/02/02 500.00
this is my footer
this is my header
ALC-D19/02 ALC 22/01/02 468.00
ALC-D35/02 ALC 22/01/02 1,142.00
ALC-D34/02 ALC 22/01/02 1,216.00
ALC-D33/02 ALC 22/01/02 0.00
and etc..etc

and whith code what you send me text file look like this :
AFR-E29/02 AFR 01/03/02 0.00
this is my header


AFR-D6/02 AFR 25/01/02 269.00
AFR-DFM2/02 AFR 31/01/02 2,500.00
AFR-E9b/02 AFR 31/01/02 0.00
AFR-D5/02 AFR 25/01/02 2,570.00
AFR-E24/02 AFR 21/02/02 0.00
AFR-E10/02 AFR 28/01/02 29,140.00
AFR-DFM9/02 AFR 14/03/02 0.00
AFR-DFM7/02 AFR 21/02/02 0.00
AFR-D7/02 AFR 29/01/02 0.00
AFR-E17/02 AFR 08/02/02 367.00
AFR-E25/02 AFR 21/02/02 0.00
AFR-E14/02 AFR 24/01/02 365.00
AFR-E13/02 AFR 24/01/02 0.00
AFR-E12/02 AFR 24/01/02 0.00
AFR-E11/02 AFR 24/01/02 306.00
AFR-DFM6/02 AFR 08/02/02 500.00
AFR-E27/02 AFR 05/02/02 0.00
AFR-E4/02 AFR 10/01/02 3,031.00
AFR-E2/02 AFR 10/01/02 3,839.00
AFR-E16/02 AFR 08/02/02 0.00
ALC-E27/02 ALC 23/01/02 500.00

this is my footer

ALC-E32/02 ALC 22/01/02 173.00
this is my header


ALC-D19/02 ALC 22/01/02 468.00
ALC-D35/02 ALC 22/01/02 1,142.00
ALC-D34/02 ALC 22/01/02 1,216.00
ALC-D33/02 ALC 22/01/02 0.00

and etc..etc

and hire is code :

Private Sub Export1_Click()
Dim db As Database, rst As Recordset
Dim a As Integer, OrderNo, Vessel, RDate, Cost, Myfile As String
Set db = CurrentDb
Myfile = "C:\_Nikola\test1.txt"
Set rst = db.OpenRecordset("Query2")
rst.MoveLast
rst.MoveFirst
Open Myfile For Output As #1
Dim bHeaderFooter As Boolean, sHeader(2), sFooter(2), iPageCount As Integer
Const LINES_PER_PAGE = 50

bHeaderFooter = True
iPageCount = 0
sHeader(0) = "this is my header"
sHeader(1) = ""
'blank line for effect
sFooter(0) = ""
'blank line for effect
sFooter(1) = "this is my footer"

For a = 1 To rst.RecordCount
OrderNo = Trim(rst!OrderN0) & Space(25 - Len(Trim(rst!OrderN0)))
'AccCode = Trim(rst!Code) & Space(8 - Len(Trim(rst!Code)))
Vessel = Trim(rst!VesselCode) & Space(5 - Len(Trim(rst!VesselCode)))
RDate = Trim(rst!RDate) & Space(10 - Len(Trim(rst!RDate)))
Cost = Trim(rst!Cost) & Space(15 - Len(Trim(rst!Cost)))
If bHeaderFooter Then
If a > 1 Then
'output a footer
For j = 0 To UBound(sFooter, 1)
Print #1, sFooter(j)
Next j
End If
End If
Print #1, OrderNo & Vessel & RDate & Cost
If bHeaderFooter Then
'output a header
For j = 0 To UBound(sFooter, 1)
Print #1, sHeader(j)
Next j
bHeaderFooter = False
iPageCount = 0
End If
rst.MoveNext
iPageCount = iPageCount + 1
If iPageCount = LINES_PER_PAGE Then
bHeaderFooter = True
End If
Next
'advance to bottom of page
For a = 1 To LINES_PER_PAGE - iPageCount
Print #1, ""
Next
'final footer
For j = 0 To UBound(sFooter, 1)
Print #1, sFooter(j)
Next j
Close #1
rst.Close
db.Close
End Sub

if is hard to make change on code maybe is easy just to explain how your code is working?
Thanks
Fule
 
Upvote 0 Downvote
Well, you have to have another If test to see when the vessel value changes. When it does, your code should do the same as when you reach the LINES_PER_PAGE.

Hope this helps :) Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Well, you have to have another If test to see when the vessel value changes.
How to make IF Test?
please Help!!!! Fule
 
Upvote 0 Downvote
Hey, fule,

An If test is Programming 101 -- AND you are a PROGRAMMER????? Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
323
SkipVought
S
Andrzejek
  • Locked
  • Question
Outlook Template replace text 2
Replies
13
Views
1K
Andrzejek
Andrzejek
K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
328
combo
combo
Andrzejek
  • Question
Copy data between SQL and Oracle
Replies
4
Views
76
Andrzejek
Andrzejek
linuxguyEastCoast
  • Locked
  • Question
export text from non-public docx, pptx, pdf
Replies
0
Views
251
linuxguyEastCoast
linuxguyEastCoast

Part and Inventory Search

Sponsor

Back
Top