Best way to print reports ??

OK these are my current options..

1. Use the microsoft WYSIYWIG code to print the contents of a Rich text box. But I am not sure if this code will print an embedded picturebox. I want to add graphs to my final report you see.

2. Build up my reports from scratch. I really need the report to look as if it was a flexigrid type as I need to colour some cells to provide differentiation.

3. Use a word doc saved as a .rtf, superimpose my data in the template,then use the RTB WYSIWYG print technique - see 1.

4. Write the report directly using the Print object, handling width and page formatting by myself.

5. Use the qPrinter class. Here a lot of the work apears top be done for me, but it means learning how to get the best from a third party class.

6. ??? Idea's anyone.

Whichever technique I eventually use I need to be able to display vertical text in the header area of the final report. This will require some alignment of individual picture box's with the vertical text within them.

I have posted this conundrum as I am not sure where best to invest my time. I would be gratefull for any views or suggestions that you guys may have on this.

TIA

my personal choice is to write to the printer object......... do all my own formatting and positioning, this take some time to setup, but i still prefer it

for testing the printing (for speed and to save reams of paper) i found i could print to a mdi child form by setting it as an object and used the same code as i would to the printer, only difference in code was .show or .enddoc, but i just error handled to suit

pete

Thanks for that Pete.

Have you tried printing to virtual PDF printer and using a PDF viewer as an alternative method to saving paper. PDF995 do a free one.

:thumb:

yes i have been using cutepdf to do the same thing, but it is still much slower than what i started doing last time, and don't get a filename dialog as well and then have to open the file in acrobatviewer to see.

the form displays on screen very fast, and i only have to select which object i am printing to, so it doesn't take much to do.

pete

Are you using the form as a print preview then ?

yes it would work as that, but doesn't have many features as other previews

pete

maybe i will develop it more at some time

When you do - don't forget to put it in the codebank ;)

:blush: Bump :blush:

We use the PRINTER object directly.

We store "report objects" in a table in SQL - and as we grab each recordset, we loop through the "report objects" to process the recordset.

Here are the "report objects" we support.

Code:

---

    ' 1    Font
    ' 2    Fontsize
    ' 3    FontBold=True
    ' 4    FontBold=False
    ' 5    FontItalic=True
    ' 6    FontItalic=False
    ' 7    Forecolor
    ' 10    Portrait
    ' 11    Landscape
    ' 12    Duplex (horizontal)
    ' 13    Paperpin
    ' 14    Papersize
    ' 20    Skip to line
    ' 21    Skip to next line
    ' 22    Header Control
    ' 23    Skip to right
    ' 24    Eject page
    ' 25    Break column
    ' 26    Specific column
    ' 27    Skip back to prior line
    ' 30    Date
    ' 31    Draw line
    ' 32    Draw left/right edge lines
    ' 33    Page
    ' 34    Query line
    ' 35    JPG
    ' 36    Draw Box
    ' 37    Vertical Print
    ' 40    Item with CR
    ' 41    Item without CR
    ' 50    Column with CR
    ' 51    Column without CR
    ' 52    Column (first-name-first) with CR
    ' 53    Column (if-not-blank) with CR
    ' 54    Column (numeric) with CR
    ' 55    Column (numeric) without CR
    ' 56    Column (numeric-nz) with CR
    ' 57    Column (numeric-nz) without CR
    ' 58    Total with CR
    ' 60    Paragraph
    ' 61    Paragraph (next 255 characters of text)
    ' 62    Load Key Column Data
    ' 63    Dump Key Column Data
    ' 64    Clear Key Column Data
    ' 70    If
    ' 71    End if
    ' 72    If-Break Area
    ' 73    End if-Break Area
    ' 74    Sub-Heading
    ' 75    End Sub-Heading
    ' 80    Sub report
    ' 81    End Sub report
    ' 82    Sub report RS loop start
    ' 83    Sub report RS loop end
    ' 100  Output column to text file

---

In this we support vertical print on a page - here's a snippet of code from that section...

Code:

---

Dim VertFont As LogFont
Dim lngPrevFont As Long, lnghFont As Long, lngRet As Long

---

Code:

---

Case 37

Debug.Print "  PrintReport>"; i; ") x="; objPrt.CurrentX; " y="; objPrt.CurrentY _
                                            ; "  Vertical Print" _
                                            ; "  tab("; prtItems(i, 2) _
                                            ; ") strRS("; prtItems(i, 3) _
                                            ; ") Y Pos="; prtItems(i, 4) _
                                            ; ") Width= "; prtItems(i, 5) _
                                            ; ") Height="; prtItems(i, 6)
lngHoldX = objPrt.CurrentX
lngHoldY = objPrt.CurrentY

f.picRotate.Cls
f.picRotate.BackColor = vbWhite

VertFont.lfEscapement = 2700    ' 180-degree rotation
VertFont.lfFaceName = objPrt.Font & Chr$(0) 'Null character at end
' Windows expects the font size to be in pixels and to be negative if you are specifying the character height you want.
VertFont.lfHeight = (objPrt.FontSize * -20) / Screen.TwipsPerPixelY
VertFont.lfWeight = 700
lnghFont = CreateFontIndirect(VertFont)
lngPrevFont = SelectObject(f.picRotate.hDC, lnghFont)

s3 = strRS(prtItems(i, 3), lngRS)
f.picRotate.CurrentX = objPrt.FontSize * 20 * 1.2
f.picRotate.CurrentY = 0
f.picRotate.Width = prtItems(i, 5)
f.picRotate.Height = prtItems(i, 6)
f.picRotate.Print s3;

objPrt.PaintPicture f.picRotate.Image, lngOffsetX + prtItems(i, 2), prtItems(i, 4) ', f.picRotate.TextHeight(s3), f.picRotate.TextWidth(s3)

lngRet = SelectObject(f.picRotate.hDC, lngPrevFont)
lngRet = DeleteObject(lnghFont)

objPrt.CurrentX = lngHoldX
objPrt.CurrentY = lngHoldY

---

The reason we have gone through all this trouble is that we can support absolutely anything our customers throw at us. Having our own report writer engine makes custom aspects a breeze.

I use #1 and a version of #3. My rtf has an object that I paste an image into, and replace fields that I have in the rtf, like [001]. I use .Find to find [001] and then .select to replace it. All of the tags are embedded in a table, so that things look very nice.

I use this method for half of the reports, and the printer object for the other half, as they are less complex.

Many thanks szlamany and dglienna. I know that both of you have contributed on these issues in the forum before and I reacall both of your techniques. Specifically ...

I apreciate the snippets that you have posted szlamany as I know the bulk of your code is propriotory. That gives me a good pointer on what area's would need to be covered if I go for the Printer object aproach.

I have tried the technique that you have mentioned dglienna. But the problem I have is that my report grid sizes are going to be dynamic. As I don't know the size of my table in advance I can't use the technique. I have checked out the generated RTF code from a word table and I don't want to have to go to the trouble of learning .rtf formatting.

Perhaps we should begin a project to provide an open source class that we could all use that covers the list that szlamany has raised ??

I am on a tight schedule at the moment so I am going to have to use a getaround or bodge for now, but would anyone be interested in contributing to such a project in the near future? PM me if you are interested in getting involved.

Perhaps you could get your company to purchase a professional reporting software like Crystal Reports Developer version?
I use CR and its so very versatile and has about every function that you could possibly need/want.

Just a suggestion. ;)

The dymanic table size issue is the reason that I use the printer object. I tried to adjust the size of the table, and ran into problems. For one report, I printed onto a second page that filled up both pages, but for dynamic objects, I had to eliminate some of the things that I would have liked, to keep the reports in the same format type.
I used to print lines using QB for DOS, and spent a long time getting things to print correctly for dynamic items. I was going to take this approch for this project, until I discovered that I could paste an image into the report, which looks a lot better than my line-sticks used to.

here is code to create easy (simple) print preview

the program i used this on prints a full page .jpg with text positioned over it with precision, the idea is to be able to complete (fill in the boxes) scanned .jpg forms and print them
the preview was only for my use during development

in general section

VB Code:

Dim tbindex As Integer, vprinter As Object

VB Code:

Select Case Command1(Index).Caption
 
 
    Case "Start"
    
      OpenFile "dc2.jpg"
 
    Case "Print"
    
        Dim p As Printer
        For Each p In Printers
            If p.DeviceName = "CutePDF Writer" Then Set Printer = p: Exit For
        Next
        Set vprinter = preview 'or p printer here
        With vprinter 'preview
'        .Move 0, 0, Screen.Width, Screen.Width / picbox.Width * picbox.Height
        sm = Me.ScaleMode: psm = .ScaleMode
        pw = .Width
            .PaintPicture picbox.Picture, 0, 0, .Width, .Height, , , picbox.Width, picbox.Height
'            .PaperSize = 9
            .FontSize = 10
            i1 = Round(picbox.Width / .Width, 2)
            i2 = Round(picbox.Height / .Height, 2)
            For i = 1 To Text1.Count - 1
                .CurrentX = 0: .CurrentY = 0
                .CurrentX = Text1(i).Left / i1: .CurrentY = (Text1(i).Top) / i2 ' - 50
 
                vprinter.Print Text1(i)
                
            Next
         on error reusme next
        .Show
        .EndDoc
         on error goto 0
        End With
 
            SendKeys "test1.pdf", True   'don't remember if this bit worked
            SendKeys "{enter}", True     ' to complete dialog for cutepdf
            
            
 
        Exit Sub
        
       Printer.EndDoc
        
    End Select
End Sub

requires mdi "prprev" and child "preview"
total code for mdi

VB Code:

Private Sub MDIForm_Load()
Move 100, 100, Screen.Width - 200, Screen.Height * 0.8
 
End Sub

code for child

VB Code:

Private Sub Form_Click()
Unload Me
Unload prprev
End Sub
 
Private Sub Form_Load()
'       Move 0, 0, Screen.Width, Screen.Width / Form1.picbox.Width * Form1.picbox.Height
       Move 0, 0, Screen.Width, Screen.Width / 8.5 * 11
 
'Width = 8.5 * 567: Height = 11 * 567
End Sub

hope this is some use, if you develop it further, please let me know

pete

You probably know all this, but just in case -

If you go down the 'use Printer object' path, then you just provide another form (does not need to be MDI), with a Picturebox on it.

When creating your Subs for doing the printing, you pass in the object that the code is to 'print' to.
If you pass in the Picturebox, then you get Print Preview.
If you pass in the Printer, it prints.
Practically all the printing commands are identical (Picturebox or Printer), and the Subs can test which it is sending to, for those few commands that differ.

www.VB-Helper.com has lots of examples.

EDIT Here's one -

http://www.vb-helper.com/howto_print_preview.html

If your graph needs anything special, Rod is very big on graphics stuff, so no doubt his site has what you need.

I too have previously suggested that we get someting going on this site.

If szlammy posts his code, we'll convert it to Net for him ;)

Quote:

---

You probably know all this, but just in case -

If you go down the 'use Printer object' path, then you just provide another form (does not need to be MDI), with a Picturebox on it.

When creating your Subs for doing the printing, you pass in the object that the code is to 'print' to.
If you pass in the Picturebox, then you get Print Preview.
If you pass in the Printer, it prints.
Practically all the printing commands are identical (Picturebox or Printer), and the Subs can test which it is sending to, for those few commands that differ.

---

i prefer the mdi form as it automatically handles the scrolling etc, i print straight to the form, not a picbox, the whole thing was done for development as simplistically as possible, it works real good for what i want to do

pete

Two questions -

I show all my Forms VBModal, and never use MDI forms.
In the interests of simplicity, couldn't one use a Form, instead of a MDI form ?
(IE would either do the same job, and the programmer can choose which to use ?)

And how does using a form, handle scrolling better ?

rob,
nothing to stop you doing that at all

the reason i used mdi, (first time i had), as the form is to represent a letter or A4 size sheet it won't fit on screen, it was simple for me to let the mdi handle the scrolling of the child form, instead of either having to write the code to scroll a picbox (or similar) or to scale the printing

pete

if anyone wants the unfinished project to test the way i have it working, pm me

Quote:

---

Perhaps you could get your company to purchase a professional reporting software like Crystal Reports Developer version?
I use CR and its so very versatile and has about every function that you could possibly need/want.

---

I am all for that so long as it works. I have not come across much good press for CR though. Are they the de-facto standard or are there other players out there.

Quote:

---

If szlammy posts his code, we'll convert it to Net for him

---

Nice try.

Thanks to you all for your feedback. I will leave the post open for now in case others can contribute as I still don't have an answer I can use. I will look into an off the shelf solution (as time is the problem not cost), and I will also look at mofdifying the qPrinter code (see first post) as it is so annoyingly close to what I need.

I looked at one of those tools, and it looked to me to be just as hard to learn their routine as it would to learn how to do it in rtf in the first place. I was going to do it all in Word, but didn't want to have to require word for my app.

I think CR is an all around good product, and it is used by a LOT of people, it used to be given away back in the NT 3.5 days. I don't know anyone that actually bought a copy of it, though. I suppose they have improved it, but for the quick and dirty report, it works, and the people that I know that had it in 3.5 are still using it.

The only bad press it's gotten is trying to deploy it from VB apps. If you use Inno, then it is pretty painless, from what I hear. Randem is around, and can help overcome most obstacles, as he developed Inno Script, and has some fine features in it.

David P.
Is paging required ?
Is there NO grid on the Form ?
Is it just a case of print what is on a Form (single page of paper) ?

If the answer to all of those was yes (no paging, no grid, single sheet of paper), I have a suggestion

Inno is a superb app, and it is good to see Randem supporting it so well around the forum.

I used crystal way back and found it's report setting up aproach (which was graphical) to be tedious and not well adapted to what I am trying to do. I would like to try it again in case they have changed it, but there is no download available from their site.

I was tempted to learn .rtf as elsewhere in my app I have given it a try loading a rich text box, but too much to do there I feel.

Writing my own driver for the printer object would be a good one - I like having low level control on my code. Probably comes from my early days as an assembler programmer - but it's time again.

I think for now it loooks like I am going to bolt on a class to the qPrinter project. Edward Moth has a good preview control and low level building blocks that wrap some good low level functionality. If I bolt on my own class I can leverage Edwards work and contribute back to it.

Quote:

---

Originally Posted by RobCrombie

David P.
Is paging required ?
Is there NO grid on the Form ?
Is it just a case of print what is on a Form (single page of paper) ?

If the answer to all of those was yes (no paging, no grid, single sheet of paper), I have a suggestion

---

LOL - I wish. I need ....

Yes to Paging
Yes to Grid
and the report is a composite and varies depending on user selections.

But thanks for the thought

Have you considered automating Excel ?

I have a very comprehensive form which displays a 'Job Register'
It is wider than Texas, and may be longer than 'War and Peace'

Printing that was going to be a nightmare.
Instead I automated Excel and mimicked the grid (SGrid-2) into Excel.
User loves it, and can use Excel's printing features (which he likes).

Quote:

---

Instead I automated Excel and mimicked the grid (SGrid-2) into Excel.
User loves it, and can use Excel's printing features (which he likes).

---

I love that Idea Rob. I may not use it as the only way of doing it but I would certainly like to place it in my code as an option.

The application is for teachers you see, and not all teachers like excell ( hence the reason for my app). Those teachers that did preffer excell would benefit from the functionality that automating it would give them.

Given that I am also a major lover and user of SGrid and THAT is what I am trying to print out I would value any input you could give me.

Even the Teachers who don't like Excel, could be advised that they don't have to 'use Excel' in the true sense.
Just introduce them to Excel's print facilities, and tell them it will be opened up for them, displaying the data, and all they have to do is go to the Print option. They can then print and get out.
(Who knows it may even be possible to add a few more commands to the automation, and do that for them as well ?)

I could send you my bas file, that is in the 'Job Register' project. However it is a bit specialised.
Normally I develop a stand alone pgm to prove a concept, then incorporate it into my real project.
If I did that, then I should send you that.
a) Not sure if I did that
b) I'm searching my drive now

Worst case scenario, I will attach the one from my project.

That would be great - thanks Rob.

This in a BAS file

VB Code:

Option Explicit
 
Private Const ROW_START As Long = 1
 
Public Sub ExportGrid(Grid As vbAcceleratorSGrid6.vbalGrid, ByVal bPrint As Boolean, Color_TOTAL_CELL_LIGHT As Long, Optional bUseCellValue As Boolean = False)
   Dim lRow As Long
   Dim lCol As Long
   
   Dim xlApp As Excel.Application
   Dim xlSht As Excel.Worksheet
   
   Set xlApp = New Excel.Application
   xlApp.Workbooks.Add
   Set xlSht = xlApp.ActiveSheet
   
   ' Grid header-
   For lCol = 1 To Grid.Columns
      xlSht.Cells(ROW_START, lCol).Value = Grid.ColumnHeader(lCol)
   Next
   'Cell values
   For lRow = 1 To Grid.Rows
      For lCol = 1 To Grid.Columns
        If bUseCellValue Then
          xlSht.Cells(ROW_START + lRow, lCol).Value = Grid.CellItemData(lRow, lCol)
        Else
          xlSht.Cells(ROW_START + lRow, lCol).Value = Grid.CellText(lRow, lCol)
        End If
      Next
   Next
   ' Draw gridlines
   xlApp.Range(xlApp.Cells(ROW_START, 1), xlApp.Cells(ROW_START + Grid.Rows, Grid.Columns)).Select
   With xlApp.Selection
      With .Borders(xlEdgeLeft)
         .LineStyle = xlContinuous
         .Weight = xlMedium
         .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeTop)
         .LineStyle = xlContinuous
         .Weight = xlMedium
         .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeBottom)
         .LineStyle = xlContinuous
         .Weight = xlMedium
         .ColorIndex = xlAutomatic
      End With
      With .Borders(xlEdgeRight)
         .LineStyle = xlContinuous
         .Weight = xlMedium
         .ColorIndex = xlAutomatic
      End With
      If Grid.Columns > 1 Then
         With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
         End With
      End If
      With .Borders(xlInsideHorizontal)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      End With
   End With
   
   ' Highlight the header using the gray color
   xlApp.Range(xlApp.Cells(ROW_START, 1), xlApp.Cells(ROW_START, Grid.Columns)).Select
   xlApp.Selection.Font.Bold = True
   With xlApp.Selection.Interior
      .ColorIndex = 15
   End With
   
   ' Highlight T O T A L S  line using the gray color
  lRow = Grid.Rows  '<== Watch out as the excel row is 1 greater than the Grid Row
  If Grid.RowItemData(lRow) = -999999999 Then
    'Make the left cell font color white, also the right cell (it may be 0 wide, but who cares)
    xlApp.Range(xlApp.Cells(lRow + 1, 1), xlApp.Cells(lRow + 1, 1)).Select
    xlApp.Selection.Font.Color = RGB(255, 255, 255)
    xlApp.Range(xlApp.Cells(lRow + 1, Grid.Columns), xlApp.Cells(lRow + 1, Grid.Columns)).Select
    xlApp.Selection.Font.Color = RGB(255, 255, 255)
    'Now cycle along the cells, setting to dark or light backcolor
    For lCol = 1 To Grid.Columns
      If Grid.CellBackColor(lRow, lCol) = Color_TOTAL_CELL_LIGHT Then
        xlApp.Range(xlApp.Cells(lRow + 1, lCol), xlApp.Cells(lRow + 1, lCol)).Select
        xlApp.Selection.Font.Bold = True
        xlApp.Selection.Interior.ColorIndex = 24
      Else
        xlApp.Range(xlApp.Cells(lRow + 1, lCol), xlApp.Cells(lRow + 1, lCol)).Select
        xlApp.Selection.Font.Bold = True
        xlApp.Selection.Interior.ColorIndex = 23           '24
      End If
    Next lCol
  End If
  
'UNCOMMENT THE NEXT BLOCK TO SEE THE COLORINDEX COLORS
'  ' Highlight a range with various ColorIndex values
'  ' 15 = gray   24 = my light T O T A L S  color   23 or 55 my (very) dark T O T A L S  color
'  Dim i As Integer
'  For i = 1 To 56
'    'xlApp.Range(xlApp.Cells(ROW_START + 1, 1), xlApp.Cells(Grid.Rows, Grid.Columns)).Select
'    xlApp.Range(xlApp.Cells(ROW_START + 1, i), xlApp.Cells(ROW_START + 1, i)).Select
'    xlApp.Selection.Font.Bold = True
'    With xlApp.Selection.Interior
'       .ColorIndex = i
'       '.Color = RGB(220, 220, 220)  'I may have to set one of the 52 colors then use that
'       '.pattern = xlPatternGray50 'xlPatternGray25 'xlSolid
'    End With
'  Next i
    
   'Size columns  and  rows
   For lCol = 1 To Grid.Columns
    If Grid.ColumnVisible(lCol) = True Then
      xlApp.Columns(lCol).ColumnWidth = Grid.ColumnWidth(lCol) / 6
    Else
      xlApp.Columns(lCol).ColumnWidth = 0
    End If
   Next
   
   For lRow = 1 To Grid.Rows + 1
    xlSht.Rows(lRow).RowHeight = 15
   Next
   
   If bPrint Then
      ' Print
      xlApp.ActiveWindow.SelectedSheets.PrintOut
      xlApp.ActiveWindow.Close SaveChanges:=False
   Else
      ' Displaying Excel
      xlApp.Cells(1, 1).Select
      xlApp.Visible = True
   End If
 
    DoEvents
    Set xlSht = Nothing
    Set xlApp = Nothing
End Sub
 
' 1440 Twips  = 1 logical Inch
'   72 Points = 1 logical Inch
' Convert my Pixels to Twips then * 72  and  / 1440
Private Function ConvertPixelsToPoints(lIN As Long) As Long
 Dim myLng As Long
  myLng = lIN / 6
  ConvertPixelsToPoints = myLng
End Function

Thanks Rob - That is a great starting point for me.

I would have posted you some points but the forum god says I have to spread a little around first :bigyello:

:thumb:

Ouch - tried to run the code and the first line got me. Until now I have not used automation and the followjng line kicks me out.

Dim xlApp As Excel.Application

Any idea as to what refs or component I need to load to get access to this object Rob?

EDIT - Sorted :blush:

I had posted a snapshot.
Now un-posted, as I guess you figured it out it needed a reference to Say Excel 10

Yes - I found that I have 9 on my PC at the moment. I am still with Office 2000

The reason I have never relied on Automation for anything before is that I didn't want to have to address M$ revision compatibility issues. Having said that if I use your code to add this as an optional feature then I get the best of both worlds

I have just finished giving it a test run and apart from the fact that it loads Excell a little slow - it proves the point well, and I will definitely find a place for this in my code. Thank you for sharing.

Quote:

---

Thank you for sharing

---

Thats ok.

Quote:

---

I have never relied on Automation for anything before is that I didn't want to have to address M$ revision compatibility issues

---

This has been my first dabble also, as MS disregard for compatibilty alarms me.

Quote:

---

apart from the fact that it loads Excell a little slow

---

There are two ways of doing this -
Early binding, and late binding.
One of those is a bit dependent on getting the correct reference (version of Excel), and one is is faster. Possibly the same one ?
I don't know which is which, and I don't know which one we are using.
I think we are using the 'I'll run with any version they've got' which I guess means late binding. and would be the slowest.
But I am unsure.

Any experts care to shed some light ?
And which do they recommend ?

I will try exporting to .CSV and opening the .CSV with Excell. Then I can format using automation.

David,

The logic to loop through an "array" of "report objects" and output data with the PRINTER object is really quite simple.

And the powerful thing about this is that you only have "one report app" to support forever.

We produce nearly 100 different reports with this single SUB at this point. For three distinct business lines.

And there are no limitations - as you get with any off the shelf product.

Here's a post from a couple of months ago about Crystal's limitations...

http://www.vbforums.com/showpost.php...1&postcount=25

So even though you say you are in a rush and need to produce this report now - do it with a loop and logic that you can use next time for your next report as well.

Here's a SELECT off of the REPORT_T table from one of our databases.

VB Code:

RptSP      RptSeq      RptWhat     RptExtra1   RptExtra2   RptExtra3   RptExtra4   RptExtra5   RptText         
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------------
rptGLTrial   1           10          0           0           0           0           0         NULL
rptGLTrial   2           1           0           0           0           0           0         Times New Roman
rptGLTrial   3           3           0           0           0           0           0         NULL
rptGLTrial   4           2           10          0           0           0           0         NULL
rptGLTrial   5           30          0           0           0           0           0         NULL
rptGLTrial   6           2           17          0           0           0           0         NULL
rptGLTrial   7           41          4900        0           0           0           0         GL Trial Balance
rptGLTrial   8           2           10          0           0           0           0         NULL
rptGLTrial   9           33          10500       0           0           0           0         NULL
rptGLTrial   10          20          2           0           0           0           0         NULL
rptGLTrial   11          41          5400        0           0           0           0         Town of Norwood
rptGLTrial   12          20          4           0           0           0           0         NULL
rptGLTrial   13          34          4750        0           0           0           0         NULL
rptGLTrial   14          20          6           0           0           0           0         NULL
rptGLTrial   22          4           0           0           0           0           0         NULL
rptGLTrial   23          41          1000        0           0           0           0         Account
rptGLTrial   24          41          2500        0           0           0           0         Description
rptGLTrial   26          41          8000        0           0           0           0         Debit Amt
rptGLTrial   27          40          10200       0           0           0           0         Credit Amt
rptGLTrial   28          20          9           0           0           0           0         NULL
rptGLTrial   29          31          11700       0           0           0           0         NULL
rptGLTrial   30          22          60          0           0           0           0         NULL
rptGLTrial   31          51          1000        0           0           0           0         NULL
rptGLTrial   32          51          2500        1           0           0           0         NULL
rptGLTrial   34          55          7500        2           16          1           0         NULL
rptGLTrial   35          54          9700        3           16          1           0         NULL
 
(26 row(s) affected)

rptSP = SPROC that will supply the RECORDSET for the report

First object - rptWhat = 10 - landscape (see prior post for list of items).

Next object - rptWhat = 1 - fontname, next = 3 - bold

Next = 2 - fontsize - get that size from the RPTEXTRA1 column - size = 10

To jump along - rptWhat = 41 - print text (GL Trial Balance) at twip position 4900

An important one is in RPTSEQ 30 - the header control - rptWhat = 22.

Basically what happens is for every row we grab from the recordset, we loop through this array.

First time through every object gets processed - simple SELECT/CASE statement.

Example of the CASE 1

Code:

---

Select Case prtItems(i, 1)
  Case 1
      Debug.Print "  PrintReport>"; i; ") x="; objPrt.CurrentX; " y="; objPrt.CurrentY _
                                      ; "  Font="; strItem(prtItems(i, 7))
      objPrt.Font = strItem(prtItems(i, 7))

---

When we hit that HEADER CONTROL (rptWhat=22) spot we remember the "spot" itself and note the size of the page in print lines (60 in this example).

Next row from the recordset - we loop through all the items again, but we skip sequence 1 through 30 because they are related to the "header".

We only do 31 through 35 - printing the 4 columns of output from the recordset. They are columns 0, 1, 2 and 3. Columns 2 and 3 are numeric so we fit them to 16 digits and right justify. The 1 in RPTEXTRA4 for these report objects indicate they will be part of a grand total.

We count the number of lines output - when we hit 60 we eject the page and reset the HEADER BOOLEAN variable so that the next row gets all array items processed for it.

Like I said this is extremely simple BASIC code - nothing complex.

And you don't want mine - it would be harder to understand then it's worth.

Develop the report you have now with this basic concept. You will have less than 10 items in your SELECT/CASE statement and a simple loop.

Next report you want to produce - use the same loop and enhance the "RPTWHAT" items as you need to support new features.

The main header text is straight forward to do so no problem there. What I really want to do is throw an array at the Printer Object and get it to work minimally with just that. OK the array is not just an array of numbers but it will be an array of types. Call it 'Cell' where ...

VB Code:

Type Cell
  DisplayedValue as String
  UnderlyingValue as Long    
  Width as Long
  Height as Long
  IsAheader as Long
  TextisVertical as Boolean     ' We could make it any angle later
  BackColor as long           ' If no value it reverts to the default 
  ForeColor as long           ' If no value it reverts to the default 
End Type
GridToDisplay() as Cell

I already display my grid information from an underlying array and so all I need to do is pass the formatting of that array, and the data, over to 'GridToDisplay' and fire it at the Print Object - where hopefully all is taken care of for me.

Hight and Width of the final grid will have to be properties that are set before the 'GridToDisplay' array is sent for printing. Also Portrait and Landscape will need to be determined in advance.

I get the gist of where you are coming from with your report handler. I will be able to use that to control the type of report allocated to a specific area within my APP. Thank you for the insight.

:thumb:

Quote:

---

Here's a post from a couple of months ago about Crystal's limitations...

http://www.vbforums.com/showpost.ph...01&postcount=25

---

Now that was an interesting link. I am glad I didn't part with any money.

Right ........ now its bugging me.

It looks like it needs to be from the ground up so I am going to do a printer object so that I have maximum flexibility. I am not going to get into doing a print preview at this stage. I will bolt that code on later.

So for now, in order that I get off on the right foot here, I would be most grateful for some considered opinions from my fellow coders on the following issues. In no particular order..

1. Should the code be written as a single class module, a module or an Active X DLL.

2. How should I handle next page?? Store all code for a page in one collection and all code for the second page in a second collection ??

3. In the context of number 2, how would I handle widows and orphans.

Some of you guys out there have already done this for yourselves. If the code is propriatry then just verbal pointers would do. At this stage code snippets may send me down a wrong line anyway.

I already have code for doing graphs in picture boxes, so that's a straight feed into the printer object.

FYI - the main thrust of going down this route is to be able to throw a Grid Array (with associated formatting information) at the printer code, and the code does a best fit into a specified rectangular space (including font scaling).

As you can probably guess most of my app is grid based. Having said that I am still going to need wrappers for handling free text.

Something like ...

VB Code:

Target.Print "This is the text to print", "Arial", eLeft, vbBlack, _
Left, Top, LeftMargin, RightMargin

if you get my drift.

Add this question to the last few....

What is the formula for font pitch in points to line spacing. Is there one ?

When we advance the print line we use a line of code that looks something like this:

Code:

---

objPrt.CurrentY = ((objPrt.CurrentY + (lngCurFontSize * 20)) \ lngTPY) * lngTPY

---

lngCurFontSize is set whenever we change fontsize with a line of code like this:

Code:

---

lngCurFontSize = Int(lngActualFontValue * 1.2)

---

lngTPY is TWIPSPERPIXELY on the device/printer you are using.

So I guess with all that said we made an assumption that there is 20% fluff space between lines. Four years ago we did a lot of digging around for something more scientific and came up short.

And in reality this has worked real well until about 2 weeks ago when we attempted to print on an old-style pre-printed receipt form for a customer. We could not quite get our lines of print to align with the "pre-printed" stuff on the form.

To get around this I added a new "report object" to my list that would add "twips" between print lines - and adding 35 twips seems to fix it for this particular form.

Keep in mind that you might set a device to fontsize 8 or 10 or 12 but when you look at it in the immediate window/debug.print it will come out to be like 11.8 - there is some interaction between twipsperpixel and fontsize...

As for your other question about how to wrap this - we do our print all in one SUB in a BASMAIN - one call to that SUB (PRINTREPORT or PRINTFORM) will output the entire report - all pages. That might break some coding rules but we went for speed with this. The code is intentionally flat - all one loop - all one CASE statement to drive the report objects. No outside functions or sub calls...

I found this link...

Quote:

---

Originally Posted by szlamany

The code is intentionally flat - all one loop - all one CASE statement to drive the report objects. No outside functions or sub calls...

---

Wow. That must be some routine.

Thanks for the history lesson, and the pointer on line spacing. Much apreciated szlamany. I can put my ruler and calculator away now ;)

I don't suppose that in your travels you have come across this code snippet...

VB Code:

Sub VertPrint(Target As Object, TextCaption As String, _
FontName As String, Xloc, Yloc, Optional AngleinDeg As Long = 90)
 
     On Error GoTo GetOut
     Dim f As LOGFONT
     Dim hPrevFont As Long
     Dim hFont As Long
     Dim FONTSIZE As Integer
     
     FONTSIZE = 10
     
     f.lfEscapement = 10 * AngleinDeg 'rotation angle, in tenths
     'FontName = "Arial" + Chr(0) 'null terminated
     f.lfFacename = FontName + Chr(0) 'null terminated
     
     If TypeOf Target Is Printer Then
          f.lfHeight = (FONTSIZE * -20) / Target.TwipsPerPixelY
     Else
          f.lfHeight = (FONTSIZE * -20) / Screen.TwipsPerPixelY
     End If
 
     hFont = CreateFontIndirect(f)
     hPrevFont = SelectObject(Target.hdc, hFont)
     Target.CurrentX = Xloc
     Target.CurrentY = Yloc
     Target.Print TextCaption
       
     ' Clean up, restore original font
     hFont = SelectObject(Target.hdc, hPrevFont)
     DeleteObject hFont
       
     Exit Sub
GetOut:
 
End Sub

It works firn for screen printing of vertical text - but it comes up short when I try to print vertical text on the printer. I can understand the frig factor needed for the target being Screen or Printer, but I thought that whatever works for the Picturebox works for the Printer, when you use .Print.

The only place it can be falling over is the reference to the Target.hdc. When we are pointing at the printer it is obviously = to Printer. But does the Printer have a .hdc?

Quote:

---

Originally Posted by Mark Gambo

I found this link...

---

Thank you for that Mark. I will file it away for later :thumb:

That looks a bit like the code snippet in post #9 that I made a couple of days ago...

We've only used the vertical print thing once so far - and I remember it being a pain to get working.

I think we settled on writing the vertical text to a picture box and then using PAINTPICTURE to get it on the printer.

If you get this working without that I'd love to see the code - really would rather do it to the printer directly.

So you did - I didn't notice it then.

Yes - I though of the picture box aproach, but I would like to be able to do it using Fonts if I can. I will keep you posted on progress.