Report headers in Excel report not printing.

[cassidybklyn]

Gentlement,
I have some weired stuff going on with my VB6/Excel module.
The first time through, my program ran and produced the desired spreadsheet with the headings and all. But subsequent runs do not produce the same result. After the spreadsheet is generated and made visible, when I click on "Print Preview" the only word that appears in the spreadsheet header is the "FALSE". Below is all the codes I am executing.

Private Sub CmdExec1_Click()
Dim dbs As Database
Dim rsin As Recordset
Dim rsout As Recordset
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWksht As Object
Dim qd As QueryDef
Dim Dbpath As String
Dim DbName As String
Dim i As Integer
Dim AccObj As Object

'Establish database path/name
'----------------------------
Dbpath = "C:\"
DbName = "PTS.mdb"

'Create an instance of Excel application, creat or open 'existing workbook and select a worksheet.
'-----------------------------------------------------------
Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("C:\BiWeeklyPeriod.xls")
Set xlWksht = xlWbk.Worksheets(1)

'Refresh - Delete current contents of excel spreadsheet
'---------------------------------------------
xlWksht.Activate
xlWksht.Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
xlWksht.Range("A1").Select

'Open the target database, point to an existing query/create one/open target recordset
'----------------------------------------------------------

Set dbs = OpenDatabase(Dbpath & DbName)
Set qd = dbs.QueryDefs("qBiWeeklyPeriod")
Set rsin = qd.OpenRecordset()

'Collect field names from input table and place them in the excel spreadsheet first row
'starting at "A1"
'----------------------------------------------------------
For i = 0 To rsin.Fields.Count - 1
xlWksht.Cells(1, i + 1).Value = rsin.Fields(i).Name
Next i

'Set the header fonts to bold
'----------------------------
xlWksht.Range(xlWksht.Cells(1, 1), xlWksht.Cells(1, rsin.Fields.Count)).Font.Bold = True

'Copy the data from the recordset to the excel spreadsheet
'---------------------------------------------------------
xlWksht.Range("A2").CopyFromRecordset rsin


'xlWksht.Sheets("Sheet1").Select
xlWksht.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
xlWksht.Range("A1").Select

'Make the excel spreadsheet visible and give control to the user
'---------------------------------------------------------------
xlApp.Visible = True
xlApp.UserControl = True

'Do spreadsheet page Setup
'-------------------------
With ActiveSheet.PageSetup
.Order = xlOverThenDown
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.CenterHorizontally = True
'.PrintTitleRows = ActiveSheet.Rows("1:1").Address
.PrintGridlines = False
.CenterHeader = .CenterHeader = "&""Arial,Bold"" NEW YORK CITY TRANSIT AUTHORITY" _
& Chr(10) & "Telecommunication and Information Services Division" _
& Chr(10) & "Project Tracking System" _
& Chr(10) & "BiWeekly Period Report" & " " & "Updated For Period Ending" & Date
.CenterFooter = "Page &P of &N"
End With

'Save the workbook: Note - Always save the workbook not the worksheets
'-----------------------------------------------------
ActiveWorkbook.Save

rsin.Close
qd.Close
dbs.Close
Set xlApp = Nothing
Set xlWbk = Nothing
Set xlWksht = Nothing

End Sub

Thanks.
Cassidy.

 

[AnotherHiggins]

     .CenterHeader = [red].CenterHeader = [/red]"&""Arial,Bold"" NEW YORK CITY TRANSIT AUTHORITY" _
      & Chr(10) & "Telecommunication and Information Services Division" _
      & Chr(10) & "Project Tracking System" _
      & Chr(10) & "BiWeekly Period Report" & "              " & "Updated For Period Ending" & Date

You doubled up the ".centerheader =". Remove one of them and it will work.

But that looks like a pretty big header - you might need to increase the Top Margin to make room for it.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

I already warned you to use qualified objects !
Replace all Selection with xlApp.Selection
Same for ActiveSheet (xlApp.ActiveSheet) and ActiveWorkbook (xlApp.ActiveWorkbook).
Furthermore, Application.InchesToPoints should be xlApp.InchesToPoints

If you use late binding, you have to replace all the xlXXX constants with their value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cassidybklyn]

Thanks. That did it.
Stars (*****) for you.
Cassidy