Best way to print reports ??

[David.Poundall]

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

[RobCrombie]

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).

[David.Poundall]

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.

[RobCrombie]

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.

[David.Poundall]

That would be great - thanks Rob.

[RobCrombie]

This in a BAS file

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

[David.Poundall]

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