|
Programatically exporting Crystal Reports to various formats like PDF, MS Word, Excel etc.
(The article demonstrates programatically opening a Crystal Report, passing values to the
required parameters and exporting to various formats using Visual Basic Code)
-- A compendious article by Mahipal Padigela
Programatically exporting Crystal Reports to various formats like PDF, MS Word, Excel etc.(arcle written by Mahipal Padigela)
Exporting Crystal Reports to various formats programatically is especially useful when you have a complex report that
takes a long time to run and you have few hundreds of reports to run and to make things worse, you have to provide the
parameters with different values each time!!
Crystal Reports software comes with 'Crystal Reports ActiveX Designer Run Time Library' which provides the necessory
objects to do this. Though I've used version 9.0 objects in the article, it should work with version 8.0 on wards, I think.
Exporting is fairly straight forward except for one thing. Sometimes, depending on your database security settings,
Crystal Reports excepts you to provide Logon credentials to each and every database table in the report, and if you have sub reports,
to each and every database table in the subreport. for this we need to loop through all the tables in the Main report and sub reports if any.
I'll briefly explain how the code works.
You need to Reference 'Crystal Reports 9 ActiveX Designer Run Time Library'
Declare the variables
Dim myApplication As CRAXDRT.Application
Dim myReport As CRAXDRT.Report
Dim myDatabase As CRAXDRT.Database
Dim myDatabaseTables As CRAXDRT.DatabaseTables
Dim myDatabaseTable As CRAXDRT.DatabaseTable
Dim mySections As CRAXDRT.Sections
Dim mySection As CRAXDRT.Section
Dim mySubreport As CRAXDRT.Report
Dim mySubreportObject As CRAXDRT.SubreportObject
Dim myParamFields As CRAXDRT.ParameterFieldDefinitions
Dim myParamField As CRAXDRT.ParameterFieldDefinition
Dim strReportPath As String
Dim strPdfPath As String
Dim strCat As String
Dim strDept As String
Dim j As Integer
Dim i As Integer
Dim X As Integer
Set default values.(Replace with your values)
strCat = "Category1"
strDept = "Dept1"
strReportPath = "C:\myCRReport.rpt"
Instantiate Objects
Set myApplication = New CRAXDRT.Application
Set myReport = myApplication.OpenReport(strReportPath)
Set myDatabase = myReport.Database
Set myDatabaseTables = myDatabase.Tables
Looping through Tables in the Main Report and supplying LOGON credentials. Please replace DSN name,database name,userid
and password to suit yours(you may get away with skipping this part of code depending on you database rights)
i = 1
For i = 1 To myDatabaseTables.Count
myDatabaseTables(i).SetLogOnInfo "DSN name","Database name","UserID","Password"
Next i
Looping through each section in the Main report, then looking for subreport objects and supplying LOGON credentials
if we find one. Please replace DSN name,database name,userid
and password to suit yours(you may get away with skipping this part of code depending on you database rights)
Set mySections = myReport.Sections
For Each mySection In mySections
For X = 1 To mySection.ReportObjects.Count
If mySection.ReportObjects(X).Kind = crSubreportObject Then
Set mySubreportObject = mySection.ReportObjects(X)
Set mySubreport = mySubreportObject.OpenSubreport
Set myDatabase = mySubreport.Database
Set myDatabaseTables = myDatabase.Tables
j = 1
For j = 1 To myDatabaseTables.Count
myDatabaseTables(j).SetLogOnInfo "DSN name","Database name","UserID","Password"
Next j
End If
Next X
Next mySection
Looping through the Parameters collection, setting values for each parameter and
disabling the crystal parameter prompting as we are supplying in the code(The report in the example has two parameters
namely 'pCat' and 'pDept'.Please replace these with yours)
Set myParamFields = myReport.ParameterFields
For Each myParamField In myParamFields
With myParamField
Select Case .ParameterFieldName
Case "pCat"
.SetCurrentValue strCat
Case "pDept"
.SetCurrentValue strDept
End Select
End With
Next
myReport.EnableParameterPrompting = False
Finally, we come to the key bit of exporting the Report to PDF
myReport.ExportOptions.FormatType = crEFTPortableDocFormat
FormatType can be crEFTWordForWindows,crEFTCharSeparatedValues,crEFTCommaSeparatedValues,crEFTExcel97,crEFTXML and many more.
Setting the export options like destination type, destination path etc and exporting
myReport.ExportOptions.PDFExportAllPages = True
myReport.ExportOptions.DestinationType = crEDTDiskFile
strPdfPath = "C:\myCRReport.pdf" 'change the file extension according to your export format
myReport.ExportOptions.DiskFileName = strPdfPath
myReport.Export (False)
Release the object references
Set myReport = Nothing
Set myDatabase = Nothing
Set myDatabaseTable = Nothing
Set myDatabaseTables = Nothing
Set mySubreport = Nothing
Set mySubreportObject = Nothing
Set myApplication = Nothing
Set myParamFields = Nothing
Set myParamField = Nothing
Private Sub Command1_Click()
Me.MousePointer = vbHourglass
Dim myApplication As CRAXDRT.Application
Dim myReport As CRAXDRT.Report
Dim myDatabase As CRAXDRT.Database
Dim myDatabaseTables As CRAXDRT.DatabaseTables
Dim myDatabaseTable As CRAXDRT.DatabaseTable
Dim mySections As CRAXDRT.Sections
Dim mySection As CRAXDRT.Section
Dim mySubreport As CRAXDRT.Report
Dim mySubreportObject As CRAXDRT.SubreportObject
Dim myParamFields As CRAXDRT.ParameterFieldDefinitions
Dim myParamField As CRAXDRT.ParameterFieldDefinition
Dim strReportPath As String
Dim strPdfPath As String
Dim strCat As String
Dim strDept As String
Dim j As Integer
Dim i As Integer
Dim X As Integer
strCat = "Category1"
strDept = "Dept1"
strReportPath = "C:\myCRReport.rpt"
Set myApplication = New CRAXDRT.Application
Set myReport = myApplication.OpenReport(strReportPath)
Set myDatabase = myReport.Database
Set myDatabaseTables = myDatabase.Tables
i = 1
For i = 1 To myDatabaseTables.Count
myDatabaseTables(i).SetLogOnInfo "DSN name","Database name","UserID","Password"
Next i
Set mySections = myReport.Sections
For Each mySection In mySections
For X = 1 To mySection.ReportObjects.Count
If mySection.ReportObjects(X).Kind = crSubreportObject Then
Set mySubreportObject = mySection.ReportObjects(X)
Set mySubreport = mySubreportObject.OpenSubreport
Set myDatabase = mySubreport.Database
Set myDatabaseTables = myDatabase.Tables
j = 1
For j = 1 To myDatabaseTables.Count
myDatabaseTables(j).SetLogOnInfo "DSN name","Database name","UserID","Password"
Next j
End If
Next X
Next mySection
Set myParamFields = myReport.ParameterFields
For Each myParamField In myParamFields
With myParamField
Select Case .ParameterFieldName
Case "pCat"
.SetCurrentValue strCat
Case "pDept"
.SetCurrentValue strDept
End Select
End With
Next
myReport.EnableParameterPrompting = False
myReport.ExportOptions.FormatType = crEFTPortableDocFormat
crEFTWordForWindows 'crEFTPortableDocFormat
myReport.ExportOptions.PDFExportAllPages = True
myReport.ExportOptions.DestinationType = crEDTDiskFile
strPdfPath = "C:\myCRReport.pdf" 'change the extension accordingly
myReport.ExportOptions.DiskFileName = strPdfPath
myReport.Export (False)
Set myReport = Nothing
Set myDatabase = Nothing
Set myDatabaseTable = Nothing
Set myDatabaseTables = Nothing
Set mySubreport = Nothing
Set mySubreportObject = Nothing
Set myApplication = Nothing
Set myParamFields = Nothing
Set myParamField = Nothing
Me.MousePointer = vbNormal
MsgBox "Finished Exporting"
End Sub
|
