Advanced Printing with Native VB Code

[VBInquirer]

Hello all, I would like to get some general ideas on this ...

I currently have an application that relies on Excel for printing. The app itself rather straightforward: the user (typically a financial professional person) enters a handful of data values, and app produces a financial planning report (about 5 pages) for a prospective client. Although the app was originally based on an Excel model, the ONLY thing Excel is used for now is to produce the report (the values computed in the program are dumped into Excel cells, no XL formulas are used).

So why use Excel at all? Well, without it, an alternative method of producing the report would be needed. At present, this app does not use a database, so that would make Crystal Reports and Access impractical for this. MS-Word might be viable. However, the report was already designed in Excel, so why re-invent the wheel? The answer is, if at all possible, I would like to produce the report without relying on an "outside" application. That leaves the native VB printing methods, which I know would require gobs of code and is an area which I have little expertise, but which would have the benefits of (a) not requiring the user to have an outside program and (b) not requiring the app to use automation code and the headaches that can sometimes cause.

Let me briefly describe what the report entails:

The cover page is the most complex from printing standpoint, as it consists of a background graphic that takes up the entire page, and then text is overlaid on top of it (consists of the agent's name, prospect's name, address info, copyright info, etc.) in various fonts with various sizes. In the Excel solution, this accomplished by embedding a picture in the worksheet, then overlaying a textbox from the "Shapes" collection (this type of textbox supports a transparent background and mixed font types/sizes).

The second page is the easiest, as it is a text-only "synopsis" in Times New Roman font.

The remaining pages consist of columns of numeric data, with borders, some with shaded or colored cells, etc., headings, and page footers (typical "Excel stuff").

Currently, the app is working well using Excel automation and report looks sharp - but I want to explore alternatives for the reasons described above.

So the question is, what would it take to produce something like this using native VB printing methods? I can imagine advanced use of the picturebox, setting points, graphic methods for drawing boxes, etc. (all the stuff I have avoided all these years

I appreciate anyone's input on this, and also, if anyone is an expert in this area and would want to take on something like this, let me know ...

Thanks for listening ...

[sigid]

...another alternative would be to use a third-party control to allow for "pretty" printing. The one that springs right to mind, since you are currently using Excel, is "Spread" from www.fpoint.com which allows you to not rely on any particular version of Excel being present on the user machine. It does, though, require you to distribute the .OCX file with your app.

Using native VB6 printing methods would not be my first choice, as the amount of code required would indeed be substantial!

[VBInquirer]

Thanks for that sigid, I actually have used Far-Point Spread on a job years ago, and I hadn't thought of something like that - the question will be, will it support the printing that I need - I will check it out.

Meanwhile, other responses most welcome ...

[westconn1]

from your description of then printing you require it should be straight forward to print to the printer object using VB, without any other progams or controls, the code below does the main things you are asking for and other stuff, the only extra thing you will need to do is fade your picture so the text shows more clearly

With vprinter 'preview
 
        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
'                Debug.Print i, .CurrentX, .CurrentY, Text1(i)
                vprinter.Print Text1(i)
                
            Next
'        prn = vprinter
        If vprinter.Name = "preview" Then
        .Show
        Else
        .EndDoc
        End If
        End With

the first part prints a full size picture, then sets the font size etc and prints text in specific positions on the page, by setting the currentX and currnetY
just change vprinter to printer and take out the .Show
note the picture was loaded into a picturebox

[RobCrombie]

I had tried in the past to get something going on this site for native VB printing, but no luck.
Do all of the pages only require single page prints ? (They don't flow on to another page because a list or grid has too many records ? )
If they are single pages, then you can use VB's controls etc to create the Form (which can be shown to user, or hiden).
You then throw that form to a bas file, which 'scans' it and sends the appropriate print commands.
This essentially gives you the equivalent of VB's PrintForm, except it is a very high resolution print.
(The same code can be told to send to another form, instead of the printer, which gives you a preview. Saves tons of paper, whilst you are 'debugging' the layout.)
I mention this approach in post 4 in this thread -
http://www.vbforums.com/showthread.p...90#post2868090
Since that post I have come across a more elaborate example than the one on the VBHelper site. It is just a case of me finding where I stored it on my computer.

Alternatively you could write to Rod Stephens (VBHelper is his site), and say to him 'Now that you have gone to VB.NET (agh!), would he mind placing his full blown version of HiRes on the web site. Or would he mind if a Good Samaritan(like me), posted it on this site".

I use HiRes in a couple of my projects, and it is terrific.

[leinad31]

Try HTML using webbrowser control.

[westconn1]

@ rob
the code i posted will print to a form or the printer with no changes, just set vprinter to the desired printer or form, i was using a MDI form to display the print out before printing, as it allowed the the form to be scrolled within the parent window

the above example was only to print a single page, as it printed a full size image, with the text positioned on the page to align with the image

[VBInquirer]

I am very appreciative of the responses on this. Thanks Pete (westconn1) and RobCrombie. To answer Rob's question about single page prints: although the number of records on a given output can vary (up to a limit of about 45 records), none of the prints overflow to the next page, but this is because in the XL solution I am using the "Fit to one page" option - therefore the XL automatically handles changes in font size, etc.) The printing method you describe in your post is intriguing and bears further investigation.

Also thanks leinad31 - I hadn't thought about HTML, that is food for thought as well.

[RobCrombie]

I found that other example -
http://www.xtremevbtalk.com/showthread.php?t=217998
It illustrates how the VB code can 'scan' (interpret) other controls on the form you wish to print.
That example does not have the option to throw to another form as a preview. However the two examples (VBHelper's simple example, and the one in the link above) could be combined into one solution which would end up being very similar to the HiRes one that I am extolling the virtues of. (Pete, you might care to check out the link as well.)

It might be possible for me to slip you a copy of HiRes ?
(I will wait to see whether there are screams of protest at the mere thought of my suggestion.)

[RobCrombie]

Let me whet your appetite(s)
This is a snapshot of HiRes running.
The Form is on the left, showing a variety of controls.
On the right is the print of that Form
It has printed onto the preview Form (instead of to the printer)
But that is exactly what would be on the paper, if it had been sent to the printer.

[RobCrombie]

PS
It is 3:30 AM in Australia, so don't think I am ignoring any responses during the next 8 hours.

[Al42]

You mentioned Crystal Reports, so assuming that you know how to create the report in Crystal (and it's easy once you learn how), just send Crystal a disconnected recordset with all your data. You don't need a database to store the data in. That gets all the reporting and preview functions into something you don't have to waste time writing.

[westconn1]

here is a link to microsofts solution to printing complete forms or screen shots as images
How To Capture and Print the Screen, a Form, or Any Window
http://support.microsoft.com/default...b;en-us;161299

[BruceG]

You guys are providing lots of good stuff here, much appreciated.

[rufaro]

hello guys
i want a code that will produces reports using vb and access the reports should be selected using from and to and produces the reports after clicking ok and the should produce reports