This is Bavaria, Germany!
These pages are written for WindowsNT/95 with Visual Basic 5 and Crystal Reports 6
Office automation means to call Winword (or other applications)
from Visual Basic and let the program do things you could do only
hard with Visual Basic. I will show you how to do this:
To work with a report I have to open the application. There's a problem. There are no Crystal Reports on the destination computer... When creating my setup diskettes with the Setup Wizard of Visual Basic a lot of Crystal Reports files get copied onto the destination machine.
My method of working with Crystal Reports
with OLE (No panic! It's easier than you might think.) makes it
necessary to tell my Visual Basic application about it. This can
be done by enabling this in Visual Basic 5:
Project - References - Crystal Report Engine 6.0 Object Library
Without that all the following works perfectly on your machine but not on your customers one...
The next thing is to DIM all the variables you need to program:
' the enum to make the call of the function friendlier in the editor
Public Enum outp
Preview
PrintOut
End Enum
Public Sub CrystalOpenReport(sCrReportFQN As String, sAccessFQN
As String, outp As OutputCrystal)
Dim appCrystal As crpeauto.Application
Dim repCrystal As crpeauto.Report
Dim dbCrystal As crpeauto.Database
Dim dbtables As crpeauto.DatabaseTables
Easy thing, eh? No, it's not. You could also write Dim appCrystal As Object but: don't do. Object is a very unspecialized object.
You should tell VB exactly
what you are trying to do if possible. There is an immediate advantage
for this. When writing your code a few lines later you get object-browsing
in the editor like usual for all the built-in object of Visual
Basic itself!
Back to the code... First lets check wether the given fully qualified path (xxxFQN-variables) for the Access Database and the CrystalReport-File (*.rpt) is correct:
On Error GoTo HandleError_CrystalOpenReportPreview
' check wether the DB exists
If Dir$(sAccessFQN) = "" Then Err.Raise 17, "CrystalOpenReport",
"Sub CrystalOpenReport: Access-database " & sAccessFQN
& " does not exist."
' check wether the Report exists
If Dir$(sCrReportFQN) = "" Then Err.Raise 17, "CrystalOpenReport",
"Sub CrystalOpenReport: Report " & sCrReportFQN
& " does not exist."
Great, the report and the database both exist. Now, lets open the Crystal Report OLE-application. If Crystal Reports is already open, open the report with that existing open program, if not create a new instance of it:
' call CrystalReports, if not open create a new one
On Error Resume Next
Set appCrystal = GetObject(, "Crystal.CRPE.Application")
If Err.Number > 0 Then
Set appCrystal = CreateObject("Crystal.CRPE.Application")
End If
On Error GoTo HandleError_CrystalOpenReportPreview
Set repCrystal = appCrystal.OpenReport(sCrReportFQN)
Set dbCrystal = repCrystal.Database
Set dbtables = dbCrystal.Tables
' Crystal Report Application is open<
The report I describe here is a simple list of persons with their phone-numbers. It's a typical report which gets its data from a query on an Access database.
| When creating the report on my computer this database is in d:\develop\bookkeeping\ms\ms.mdb and the queries and table in it. The Crystal report works perfect on my machine but will NOT work on another machine with another directory-structure. |
Paths on my machine:![]() |
This means that I have to manipulate the properties of the tablelinks in the report on the remote machine to tell the report over there where it can access its data. This is the codesnippet for that:
' there is only one database the report depends on
' set all links to tables in the report to the current database
For i = 1 To dbtables.Count
dbtables.Item(i).Location = sAccessFQN
Next i
That's it... stop that's not all. I would have forgotten that I wanted to print out the report...
Select Case outp Case Preview: repCrystal.Preview Case PrintOut: repCrystal.PrintOut End Select
Not to forget the errorhandler, but now we are ready.
HandleError_CrystalOpenReportPreview:
If (Err.Number <> 0) Then
MsgBox (Err.Description)
End If