Crystal Reports

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

Full Code

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

Top

www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.