Print complex reports with Visual Basic design

I. Create an external Excel object with VB

Most large activex-enabled applications and other ActiveX parts provide a top-level, externally-created object in their object hierarchy. This object provides access to other objects in the hierarchy and also provides methods and properties that work with the entire application.

For example, each MicrosoftOffice application provides a top-level application object. The following statement shows a reference to the article object for the microsoftoffice/9.shtml ' target= ' _blank ' class= ' >excel ' application:

Dimxlappasexcel.application

Setxlapp=newexcel.application

You can then use these variables to access the subordinate objects in the Excel application, and the properties and methods of those objects. For example:

Setxlapp=createobject ("Excel.Application")

' Activate Excel application

Xlapp.visible=false ' Hide Excel application window

Setxlbook=xlapp.workbooks.open (strdestination)

' Open the workbook, strdestination as an Excel report file

Setxlsheet=xlbook.worksheets (1)

' Set the worksheet

Two. Design the report template file with EXCEL97

EXCEL97 is a very good tool for creating reports. It provides the ability to merge, split, and draw any of the cells that are basically designed to meet the requirements of designing all the complex reports. It provides a powerful support for the arbitrary control of the format of any cell and the more arbitrary design of the report.

Based on the user-supplied reports, we can quickly generate template files in Excel. The so-called generation template file is designed to meet the needs of many users. It's also a bit of a preparation for a change in the report. For example, the user needs to print hundreds of employee resumes, but its format is consistent, and with the time and the actual situation changes, the table format may need to change, we design a template document can obviously "status quo".

When you build a worksheet, we should record the number of cells that you want to fill in and the data fields that you want to fill in the cell. This forms a form that is self-explanatory when writing a program. Such as:

Cell (4,2) Staff name Cell (6,6) Graduate School

Cell (4,4) Staff gender cell (6,7) major

Cell (4,6) Staff National Cell (6,9) Working hours

(Table i)

In the program, of course, we do not operate on the template file, we only need a copy of the template file to operate on the line (this is also our design template file for a purpose and benefits). As in the following example:

Dimstrsource,strdestinationasstring

strsource=app.path& "Excelsregisterfee.xls"

' Registerfee.xls is a template file

strdestination=app.path& "Excelstemp.xls"

Filecopystrsource,strdestination

' Copy template files to a temporary file

Three. Generate Worksheet content

With the above two steps to pave the way, we will then just according to (table I) in the format of the cells assigned to the value. Such as:

DatPrimaryRS.Recordset.MoveFirst

' Datprimaryrs as Data control

Ifisnull (datprimaryrs.recordset! name) =falsethen

Xlsheet.cells (4,2) =datprimaryrs.recordset! name

EndIf

Ifisnull (datprimaryrs.recordset! Sex) =falsethen

Xlsheet.cells (4,4) =datprimaryrs.recordset! sex

EndIf

Ifisnull (datprimaryrs.recordset! Nation) =falsethen

Xlsheet.cells (4,6) =datprimaryrs.recordset! ethnic group

EndIf

Four. Print the report

Once you have generated the worksheet, you can print instructions to Excel.

Note You should perform a save operation on the Excel temp file before you perform the print operation, so that the user is puzzled if Excel prompts the user to save the modified file after exiting the application. The following statement:

Xlbook.save ' Save file

Xlsheet.printout ' Perform print

xlApp.Quit ' Exit Excel

So readers should see that our design of the report printing is through the Excel program to implement the background. Users simply do not see the specific process, they only see a beautiful report is easy to print out.