Link to home
Start Free TrialLog in
Avatar of suehwee
suehwee

asked on

report export problem using CRViewer

i am using visual basic 6.0, CrystalReport8.0 and Windows2000.

i use CRViewer and wannna to export my report to rich text format and excel format.
but i found the exported report is 0 byte
why all my exported reports are empty?

then 2nd time the report is exported to rtf and xls format
the error message is  "File Not Found"

anybody can help me?
thanks.
Avatar of Mike McCracken
Mike McCracken

What code are you using in VB to export the report?

mlmcc
Are you using the RDC or the OCX in vb?
Avatar of suehwee

ASKER

i am using RDC.
and is it need hard code for Export Function?
sorry because i am first time to try this.
here is my code:

'**** Define Parameters Name ********
Dim SDate As String
Dim EDate As String
Dim STime As Date
Dim ETime As Date
Dim SGroup As Integer
Dim EGroup As Integer
Dim SID As Integer
Dim EID As Integer
Dim SButton As Integer
Dim EButton As Integer
Dim XWait As Date
Dim YTrans As Date
Dim TotalAvgWait As String
Dim TotalTransCust As String
Dim TotalTransTran As String
'**************************************

Private Sub Form_Load()
'*** Passing Value to Report Parameter ***
SDate = Parameter.dtpDateFrom.Value
EDate = Parameter.dtpDateTo.Value
STime = Parameter.METimeFrom.Text
ETime = Parameter.METimeTo.Text
SGroup = Parameter.txtGroupFrom.Text
EGroup = Parameter.txtGroupTo.Text
SID = Parameter.MEIDFrom.Text
EID = Parameter.MEIDTo.Text
SButton = Parameter.txtButtonFrom.Text
EButton = Parameter.txtButtonTo.Text
XWait = Parameter.MEX.Text
YTrans = Parameter.MEY.Text
TotalAvgWait = DataShow.AA_AvgWait
TotalTransCust = DataShow.TT_AvgWait
TotalTransTran = DataShow.CC_AvgWait
'*******************************************

Dim crApp As New CRAXDRT.Application
Dim crRep As New CRAXDRT.report
Dim i As Integer

'load a report created in the designer
Screen.MousePointer = vbHourglass
Set crRep = crApp.OpenReport("Periodic.rpt")
CRViewer1.ReportSource = crRep

    'disable the parameter prompting
    crRep.EnableParameterPrompting = False

    'Pass Parameters to CrystalReport
    crRep.ParameterFields(1).AddCurrentValue SDate
    crRep.ParameterFields(2).AddCurrentValue EDate
    crRep.ParameterFields(3).AddCurrentValue STime
    crRep.ParameterFields(4).AddCurrentValue ETime
    crRep.ParameterFields(5).AddCurrentValue SGroup
    crRep.ParameterFields(6).AddCurrentValue EGroup
    crRep.ParameterFields(7).AddCurrentValue SID
    crRep.ParameterFields(8).AddCurrentValue EID
    crRep.ParameterFields(9).AddCurrentValue SButton
    crRep.ParameterFields(10).AddCurrentValue EButton
    crRep.ParameterFields(11).AddCurrentValue XWait
    crRep.ParameterFields(12).AddCurrentValue YTrans
    crRep.ParameterFields(13).AddCurrentValue TotalAvgWait
    crRep.ParameterFields(14).AddCurrentValue TotalTransCust
    crRep.ParameterFields(15).AddCurrentValue TotalTransTran
   
'Print Preview at the report
CRViewer1.ViewReport
'Zoom the preview window to 100%
CRViewer1.Zoom 100
Screen.MousePointer = vbDefault

Set crRep = Nothing
Set crApp = Nothing

End Sub
Avatar of suehwee

ASKER

besides that, my VB missing  the reference of CrystalReport8 ActiveX Designer Design Time Library.
what is that?
i try to download some examples for using CRViewer
but i cannot run the example because "CrystalReport1.dsr" is not found.

I am assuming the report shows in the viewer.
Try this.

Move this line of code
CRViewer1.ReportSource = crRep

Put it between these lines

'Print Preview at the report
CRViewer1.ViewReport


'Print Preview at the report
CRViewer1.ReportSource = crRep
CRViewer1.ViewReport

You may need to add
CRViewer1.Show


Delete these lines from the load event.  Put them in the form close event

Set crRep = Nothing
Set crApp = Nothing


Since the viewer is still trying to use the report and the application (to export and show again) you can't delete them until the form closes.


CrystalReport8 ActiveX Designer Design Time Library is the library that allows you to design reports in VB.  The way you are using the RDC you don't need it you need
CrystalReport8 ActiveX Designer Run Time Library
Look for craxdrt.dll.  You load it under PROJECT--> REFERENCES.

If you don't have it make sure you bought the CR Developer's Edition.

CrystalReport1.dsr is the VB embedded version of a CR Report that should have come with the sample you downloaded.  I would recommend you stay away from using the .dsr files and the Designer Design Time Library.

If you need anything more just ask
mlmcc
Avatar of suehwee

ASKER

mlmcc,
sorry , it still cant be done after i tried.
but the problem of "File is not found" can be solve already.

but i face a runtime error of R6025: "pure virtual function call"

i am using the CrystalReport Professional 8.0.
so, should i reinstall CR Developer 8.0?

regards
'create the objects
Set RunCrys = New CRAXDRT.Application
Set RunRpt = New CRAXDRT.Report
'open the report  
Set RunRpt = RunCrys.OpenReport([report path])

RunRpt.Database.Tables(1).SetLogOnInfo CStr([Server]), CStr([Database]), CStr([Username]), CStr([Password])
RunRpt.ExportOptions.FormatType = crEFTExactRichText
RunRpt.ExportOptions.RTFExportAllPages = True
RunRpt.ExportOptions.DestinationType = crEDTDiskFile
RunRpt.ExportOptions.DiskFileName = SendTo
RunRpt.Export False

This is code i currently usefor the rdc to export to rtf... try this and let me know how it goes. you may also try to call the parameter fields by name, and be sure that all the values are putting the proper data in the parameter fields. It could be due to the parameters, and the report may be pulling back 0 records. another way to check this would be to msgbox or debug.print for each parameter value to be sure that it is correct some thing like

msgbox crRep.ParameterFields(1).name
msgbox SDate

and if these parameter values are dates and stuff you need to be sure you do the cdate(SDate) and try the SetCurrentValue

crRep.ParameterFields(1).SetCurrentValue (CDate(SDate))
If you have the developer version I would install it.  I would recommend you uninstall the Professionsal version first.  The developer version is Professionsal with added developer tools.

Any idea where the runtime error of R6025: "pure virtual function call"  happens?

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of damion69
damion69

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of suehwee

ASKER

thanks mlmcc damion69!
but when i test my application in windows98
it seem dunt have such problem.
it can export to RTF and excel format as well.

but how it will happen in windows 2000?

regards,
Avatar of suehwee

ASKER

may i know what is the differences to use the CR8.0 and CR8.5?
which one has the better feature?

damion69 - CRViewer1 is the default name for the CR Viewer control on the form.  Since it is a control it isn't in a dim statement.

I wonder since the program works under Win98 but not under Win2K could it be a permission issue?  Does the program or the user have write permissions to the directory in question?  We had that problem with the initial install of Win2K, users didn't have permissions except in their  MY DOCUMENTS.

mlmcc
Avatar of suehwee

ASKER

so.. is there any solution?
or another way is to use OCX method?

regards
Yes there are other solutions.

Is what you get when you export to Excel what you expected?
Most of my users when they export to excel want the data in columns so they can manipulate it in some way.  I wrote code that takes a recordset and exports it to an excel spreadsheet.  One column per field.

If you want to see that I'll try to find it and post it.

mlmcc
Avatar of suehwee

ASKER

to mlmcc:

so, should i still using CRViewer or OCX?
can u pls give me some advices?

then for the exporting, i want to export my report to at least 3 types: RPT, RTF, and excel (in columns ) because it is more flexible in what u had mention before.

and thank to u again. and waiting ur reply soon.

regards,
I have switched over to using the CRViewer completely.  I used the OCX because I  didn't understand how to use the viewer without importing and creating a .dsr file.  

I now have learned (from this site) how to use the .rpt files through the CRViewer.  The code you gave is very similar to what I do.  I'll try to find my code where I export to a mail message and see if that will give you any ideas.

I will also find the code I use to export to an Excel file.

mlmcc
Avatar of suehwee

ASKER

hi mlmcc,

i got another way to view crystal report
but i got problem if to pass to many parameters...
here is example to pass only 2 parameters

go to component to add the crystal report control first

'**** Define Parameters Name ********
Dim SDate As String
Dim EDate As String
'************************************

CrystalReport1.ReportFileName = App.Path & "\Rpt.rpt"
CrystalReport1.Destination = crptToWindow
   
'Pass Parameters to CrystalReport
CrystalReport1.ParameterFields(1) = "SDate;" & Parameter.dtpDateFrom.Value & ";true"
CrystalReport1.ParameterFields(2) = "EDate;" & Parameter.dtpDateTo.Value & ";true"

CrystalReport1.WindowState = crptMaximized
CrystalReport1.Action = 1
'***************************************
look at
https://www.experts-exchange.com/questions/20330936/Export-Soft-Copy-to-local-drive-Crystal-Report-7-0.html

I included the code I use to export to a mail message.

Here is the code I use to export to a spreadsheet with one database column/field per excel column
One database record per row


Public Sub Create_Rpt_Spreadsheet(VIEW_NAME As String, _
                         Where_Clause As String)

Dim rs_Data As ADODB.Recordset
Dim str_Select As String

Dim rs_Cols As ADODB.Recordset
Dim str_Cols As String

Dim objExcel As Excel.Application

Dim numRows As Integer
Dim numCols As Integer

Dim row As Integer
Dim col As Integer

On Error GoTo Err_SS_Recordset

    Screen.MousePointer = vbHourglass

'
'   Create recordset
'
    str_Select = "SELECT  *  FROM  " & VIEW_NAME
    If (Where_Clause <> "") Then
        str_Select = str_Select & "  WHERE  " & Where_Clause
    End If
   
    Set rs_Data = New ADODB.Recordset
    rs_Data.CursorLocation = adUseClient
    rs_Data.Open str_Select, cn, adOpenStatic, adLockReadOnly
    If (rs_Data.RecordCount <= 0) Then
        MsgBox "No data to export to Excel for your filter.", vbOKOnly, "No Data"
        rs_Data.Close
        Set rs_Data = Nothing
        Exit Sub
    End If
   
On Error GoTo Err_Create_Rpt_Spreadsheet

'
'   Initialize number of rows and columns
'
    numCols = rs_Data.Fields.count
    numRows = rs_Data.RecordCount
   
'
'   Create Excel Spreadsheet
'
    Set objExcel = New Excel.Application
    objExcel.Workbooks.Add

'
'   Set defaults for the spreadsheet
'
    objExcel.Cells.Select
    With objExcel.Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With

'
'   Open form showing export progress
'     frm_Export2Excel is simply a form about the size of a typical message box with a progress
'     bar on it.
'
    frm_Export2Excel.Show
    frm_Export2Excel.lbl_Exp2Excel.Caption = "Header Row  "
   
'
'   Load column headers
'
    str_Cols = "SELECT  *  FROM  view_Excel_Col_Info where txt_ViewName = '" & VIEW_NAME & "'"
    Set rs_Cols = New ADODB.Recordset
    rs_Cols.Open str_Cols, cn, adOpenStatic, adLockReadOnly
    row = 1

'
'   Use the information from the Excel Column Information table if it exists.
'   Use the view field name otherwise
'   This code is specialized to a table we have with deined column names and widths
'    you can use it to get an idea how to format and control column widths
'    rs_Colc!int_ColWidth is just an integer
'    rs_Cols!txt_ColName is a string
'
    If (rs_Cols.RecordCount > 0) Then
        For col = 1 To numCols
            rs_Cols.MoveFirst
            rs_Cols.Find "txt_FieldName = '" & rs_Data.Fields(col - 1).Name & "'"
            objExcel.Cells(row, col) = Trim(rs_Cols!txt_ColName)
            objExcel.Range(objExcel.Cells(row, col), objExcel.Cells(row, col)).Select
            objExcel.Selection.ColumnWidth = rs_Cols!int_ColWidth
            If (InStr(rs_Cols!txt_ColName, "Date")) Then
                objExcel.Range(objExcel.Cells(row + 1, col), objExcel.Cells(row + rs_Data.RecordCount, col)).Select
                objExcel.Selection.NumberFormat = "dd-mmm-yyyy"
            End If
        Next col
    Else
        For col = 1 To numCols
            objExcel.Cells(row, col) = Trim(rs_Data.Fields(col - 1).Name)
            objExcel.Range(objExcel.Cells(row, col), objExcel.Cells(row, col)).Select
            objExcel.Selection.ColumnWidth = 8
        Next col
    End If
'
'  Update the progress bar
'    Hate to have users wondering if the program is stuck or working
'
    frm_Export2Excel.pb_Exp2Excel.Value = 100# * row / (numRows + 1)
'
'   Close the column information recordset
'
    rs_Cols.Close
    Set rs_Cols = Nothing

'
'   Load selected records into rows
'

    rs_Data.MoveFirst
    For row = 2 To numRows + 1
        frm_Export2Excel.lbl_Exp2Excel.Caption = "Row " & str(row - 1) & " of  " & str(numRows)
        frm_Export2Excel.pb_Exp2Excel.Value = 100# * row / (numRows + 1)
        For col = 1 To numCols
            objExcel.Cells(row, col) = rs_Data.Fields(col - 1).Value
        Next col
        objExcel.Rows(row).Select
        objExcel.Selection.RowHeight = 15
        rs_Data.MoveNext
    Next row

'
'   Activate the Excel spreadsheet
'
    Unload frm_Export2Excel
   
    objExcel.Range("A1").Select
    objExcel.Visible = True
    Screen.MousePointer = vbDefault

Exit_btnCancel_Click:
    rs_Data.Close
    Set rs_Data = Nothing
    Set objExcel = Nothing
    Exit Sub
   
Err_SS_Recordset:
    MsgBox Err.Description
    Set rs_Data = Nothing
    Screen.MousePointer = vbDefault
    Unload frm_Export2Excel
    Exit Sub

Err_Create_Rpt_Spreadsheet:
    MsgBox Err.Description
    objExcel.Visible = True
    Unload frm_Export2Excel
    Resume Exit_btnCancel_Click

End Sub


good luck
mlmcc
Hi suehwee,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: mlmcc and damion69

suehwee, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

mlmcc, points for you at:
https://www.experts-exchange.com/questions/20570985/points-for-mlmcc-re-20342486.html