You are here: > ESRI Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - ArcObjects Visual Basic for Application (VBA) forum

Running a Crystal Report in VBA   Mark Haberle Jun 26, 2002
Re: Running a Crystal Report in VBA   arun kumar rote Jul 23, 2002
Re: Running a Crystal Report in VBA   Kuang-Yao Lee Oct 09, 2002
Re: Running a Crystal Report in VBA   Chuck Potalivo Oct 30, 2002
Re: Running a Crystal Report in VBA   J Foreman Jul 11, 2004
Re: Running a Crystal Report in VBA   Chuck Potalivo Jul 12, 2004
Re: Running a Crystal Report in VBA   J Foreman Jul 29, 2004
Re: Running a Crystal Report in VBA   Andrey Khataev May 20, 2005
Re: Running a Crystal Report in VBA   Marcelo Velasquez Jul 14, 2006
Re: Running a Crystal Report in VBA   Mary Burge Dec 12, 2007
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Running a Crystal Report in VBA 
Author Mark Haberle 
Date Jun 26, 2002 
Message Can someone provide some insight into running a crystal report in VBA. I would like to create a report template (or report [*.rpt]) and then run it with a UIControl.

So the user would simply click the button and up pops the report.

I would like to query them for a couple key ingredients (gdb path and name, report title, use selection set, etc.) and then invoke the crystal wizard.

I have seen the ADO example and am not convinced that is what I want. I have also been able to use:

ThisDocument.CommandBars.Find(arcid.ReportObject_CrystalReportWizard).Execute

but I want to orchestrate that interface and make it "transparent" to the user.

any hints would be greatly appreciated. 
  ~~~~~~~~~~~~~~~~~~~
Mark Haberle
Applied GIS, Inc.
www.appliedgis.com
~~~~~~~~~~~~~~~~~~~ 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author arun kumar rote 
Date Jul 23, 2002 
Message Before executing below code Put the CrystalReport control on your form. 
 
CrystalReport1.ReportFileName = gStrReportsPath & "\" & str
    CrystalReport1.Connect = "DSN=scana"
    CrystalReport1.SQLQuery = "SELECT CATCHTRY.STREET,CATCHTRY.YEAR,ESTAB.ES_NAME,CATCH.LEA FROM CATCHTRY,ESTAB WHERE CATCHTRY.CODE=ESTAB.ES_ESTAB " & strOrderByClause
    CrystalReport1.Destination = crptToWindow
    CrystalReport1.WindowState = crptMaximized
    CrystalReport1.Action = 1
    CrystalReport1.Reset
 
  arunkumar@infotech.stph.net 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Kuang-Yao Lee 
Date Oct 09, 2002 
Message Sometimes I have an error message:
CRPaige library (CRPaige32.dll) not found.
It should be in the path. Do you know why?

 
  Sr. GIS Data Steward
South Florida Water Management District 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Chuck Potalivo 
Date Oct 30, 2002 
Message can you provide me with any more information about using crystal reports in vba, or perhaps just tell me where you got all your information? 
  Chuck Potalivo MCSE 2000,A+,Net+
Carroll Engineering Corporation
Warrington, PA
GIS Department
(AML/VC++/VB/VBA Programmer) 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author J Foreman 
Date Jul 11, 2004 
Message Hi,

I realize that your post is now about 2 years old, but I was wondering if you ever found what you were looking for in terms of programming Crystal Reports? I am using Crystal Reports 9 with ArcGIS 8.3 and looking for a way to automate some processes. Perhaps you can share some of your prior knowledge with me...?

Best,
J. Foreman
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Chuck Potalivo 
Date Jul 12, 2004 
Message There are alot of ways to use Crystal Reports from VBA, or VB, or any other ActiveX or COM compatible Language. You need to set a few references in your project if you want to have your report open in the crystal reports main program. OR you can simply place the crystal reports viewer activex control on a form and have you rreport be view from there, with the ability to print and a few other things. Simple interface too. If you just browse your references list you will see several Crystal Reports entries, and the viewer control is even listed under your controls list too, just take a look. Some keywords you can search for on the internet to find information on how to use all of these things are: Report Viewer Component; crviewer; Crystal Reports ActiveX Designer Run Time Library (this is also the reference you need to set to use it); Create TTX files (you will need to find out about this if you want to use Active (Live) Data in your reports); CRAXDRT (this is the name of the core that all of the objects that you use are derived from)...

Below I'm posting some sample code on how to view a simple report with the Report Viewer Control, any questions, just post back.


 
 
' You  must first initialize the Main Application Object, this is much like opening the Crystal Reports program
  Dim crApp         As CRAXDRT.Application
  Set crApp = New CRAXDRT.Application
          
  ' Then you must initialize the Main Report Object, this is like opening a report in the Crystal Reports program
  Dim crReport      As CRAXDRT.Report
  Set crReport = crApp.OpenReport("C:\Test.rpt")
        
  ' This sometimes improves the loading time for large reports.
  crReport.UseIndexForSpeed = True
        
  ' Since we are assuming then our report uses Active Data (takes an ADO Recordset as it's data source, see "TTX files")
  ' We need to get a reference to the database tables that the report is going to use.
  Dim crTables      As CRAXDRT.DatabaseTables
  Set crTables = crReport.Database.Tables
        
  ' Next we need take a specific table, in this case, since we are going to assume there is only one Active Data source,
  '  there is only one source table in the report, so we take the first table.
  Dim crTable       As CRAXDRT.DatabaseTable
  Set crTable = crTables.Item(1)

  ' Using the first table's reference, we set it's data source to a recordset (pRecordset is an ADO recordset) that we have   
  ' previously initialize in our program.  The second parameter is always going to be 3, regardless.
  ' I cannot remember if you can use ArcObjects recordsets here, but I believe you can, I have used this coding in an ArcObject
  '  VBA program before, so there IS a way.
  crTable.SetDataSource pRecordset, 3
      
  ' Load the form that has the Crystal Reports Viewer Control (named: crvMain)
  Load frmView
        
  ' Set the Report Viewer's Report Source property to our Main Report Object
  frmView.crvMain.ReportSource = crReport
  
  ' I like to set the form's caption to include the title of the report, you can access many of the reports properties at
  '  this point      
  frmView.Caption = "Crystal Reports Viewer - [ " & crReport.ReportTitle & " ]"

  ' This call actually loads the report, and loads all of the data into the template.  This is like going from design-time to
  '  run-time.
  frmView.crvMain.ViewReport
  
  ' Show the form, you may want to call this before calling the ViewReport method, but you wont be able to make it Modal      
  frmView.Show vbModal
 
  Chuck Potalivo MCSE 2000,A+,Net+
Carroll Engineering Corporation
Warrington, PA
GIS Department
(AML/VC++/VB/VBA Programmer) 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author J Foreman 
Date Jul 29, 2004 
Message Hi Chuck,

Thank you very much for your help - I appreciate it greatly. I just received word of your response today, so I apologize for not returning a reply sooner!

I had been given the runaround by Business Objects and ESRI re. the use of CR and ArcMap, and its interactivity!

I have a few more specific questions to ask you, but I do not want to "dominate" the discussion forum... Please email me off-list at mayadog4@comcast.net if you have a moment!

Thanks again for your support.
J Foreman

mayadog4@comcast.net
jay@redpawtechnologies.com

- - - - - - - - - - - - - - -

Red Paw Technologies, Inc.
Pleasant Hill, California
925-521-9889 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Andrey Khataev 
Date May 20, 2005 
Message Dear Chuck!
I'm trying to do as you advised. I'm added Crviewer control and added reference to Crystal Reports 8.5 ActiveX Designer Runtime Library.
But it calls en error: Server Has not yet been opened. What does it mean?
The code is posted below: 
 
Dim crApp As CRAXDRT.Application
    Set crApp = New CRAXDRT.Application
          
  ' Then you must initialize the Main Report Object, this is like opening a report in the Crystal Reports program
  Dim crReport As CRAXDRT.report
  Set crReport = crApp.OpenReport("d:\temp\gis\astrakhan\exported\reports\PortPassport_Y_ODBC.rpt", 1)
  CRViewer1.ReportSource = crReport
  CRViewer1.ViewReport
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Marcelo Velasquez 
Date Jul 14, 2006 
Message Hi

What Version Crystal you is using ?

The Version Crystal Reports 9 for ESRI do not have a option for to build a TTX file or Build a report through TTX file , When I tried to use TTX file show me error:

"QUERY ENGINE ERROR, NO DATABASE DLL HAS BEEN SPECIFIED"
 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Running a Crystal Report in VBA 
Author Mary Burge 
Date Dec 12, 2007 
Message i have code very similar to yours using vb6 and cr xi - my sql does pare my input down to 1 record per execution - however, my report pops up but only displays numeric fields, no string data. i also have a blob field coming from my data set that doesn't print but at this point i need to get the easy stuff working first. any suggestions? thanks in advance (been stuck on this for way too long!) 
 
Public Sub Exist_Report(pWkspace As IWorkspace, ControlNo As String)
On Error GoTo Report_fail:

'++ ADO Objects
 Dim pAdoCon As ADODB.Connection
 Dim pAdors As ADODB.Recordset
 
 '++ FDOADOConnection object
 Dim pFdoCon As IFDOToADOConnection
 Set pFdoCon = New FdoAdoConnection

 '++ Create a NEW ADO Connection object from the workspace
 Set pAdoCon = pFdoCon.CreateADOConnection(pWkspace)
 
 '++ Crystal Report Objects
 Dim pProj As CRAXDRT.Application
 Dim pReport As CRAXDRT.Report
 
 Set pProj = New CRAXDRT.Application
 Set pReport = pProj.NewReport
 

 '++ Create and open a recordset
 Dim SQLStr As String
  
 SQLStr = "Select * from Planned where CONTROLNO = '" & ControlNo & "'"
 Set pAdors = New ADODB.Recordset
 
 pAdoCon.CursorLocation = adUseClient
    
 pAdors.Open SQLStr, pAdoCon, adOpenStatic, adLockOptimistic
        
 Load CrystalReport
 CrystalReport.Show vbModeless
           
    'Open Crystal Report

   If pAdors.RecordCount <> 0 Then
      Set pReport = pProj.OpenReport("C:\Documents and Settings\mmburge\My Documents\ESRI Crystal Reports\NEW ADDRESS.rpt ")
      pReport.DiscardSavedData
      pReport.Database.SetDataSource pAdors
      'pReport.ParameterFields.GetItemByName("pCONTROLNO").AddCurrentValue (ControlNo)
      
      'Dim objReport As New ReportDocument

      'Dim rptTxtGroup As TextObject
      'rptTxtGroup = pReport.Item("SALUTATION1")
      Dim fieldvalue As Long
      Dim strField As String
      'If pReport.Database.Tables(0).Fields("SALUTATION").Value = 1 Then
        fieldvalue = pReport.Database.Tables(1).Fields.Item(3).Value
        
        strField = pReport.Database.Tables(1).Fields(4).Value
      'If pReport.Database.Tables.Item.Fields.GetItemByName("SALUTATION1") = 1 Then
        'pReport.Database.Tables.Item.Fields.GetItemByName UCase("salutation") = "Mr."
        'MsgBox "GOT ONE"
      'End If
      'pReport.
      
      CrystalReport.CAXRV1.ReportSource = pReport
      'CrystalReport.CAXRV1.Refresh
      CrystalReport.CAXRV1.ViewReport
      'pReport.PrintOut False, 1, , 1
      CrystalReport.CAXRV1.PrintReport
      'Do While CRViewer.IsBusy                        'ZOOM METHOD DOES NOT WORK WHILE
        'DoEvents                                             'REPORT IS LOADING, SO WE MUST PAUSE
      'Loop                                                            'WHILE REPORT LOADS.

      'CRViewer.Zoom 94
      
      'pReport.ExportOptions ("C:\911Ltr\" & pFeat.Value(pFeat.Fields.FindField("ControlNo")) + ".PDF")
      
   Else
      MsgBox "Problem Viewing Public Access Report...."
   End If

'End With
Exit Sub
Report_fail:
   MsgBox "ADO_report module : " & Err.Number, Err.Description, vbInformation
   Resume Next
End Sub