VB 6.0 - vb6 report in excel - Asked By madhavi on 15-Dec-11 12:38 AM

Hi,

can any one please tell me iam new to vb6.

i need to show vb6 crystal report preview  in excel.Is it possible..
(when i run the program and click print that time it wil show print/preview/... that preview i click means that data i wan to show in excel)
Please any one help me.Its very urgent..


Thanks in advance.
Aman Verma replied to madhavi on 15-Dec-11 12:43 AM

Select the default form (Form1.vb) you created in VB.NET and drag two buttons (Button1, Button2 ) and CrystalReportViewer control to your form.
Select Form's source code view and import the following :
Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
    Dim cryRpt As New ReportDocument

    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
    cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")
    CrystalReportViewer1.ReportSource = cryRpt
    CrystalReportViewer1.Refresh()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click
    Try
      Dim CrExportOptions As ExportOptions
      Dim CrDiskFileDestinationOptions As New _
      DiskFileDestinationOptions()
      Dim CrFormatTypeOptions As New ExcelFormatOptions
      CrDiskFileDestinationOptions.DiskFileName = _
            "c:\crystalExport.xls"
      CrExportOptions = cryRpt.ExportOptions
      With CrExportOptions
      .ExportDestinationType = ExportDestinationType.DiskFile
      .ExportFormatType = ExportFormatType.Excel
      .DestinationOptions = CrDiskFileDestinationOptions
      .FormatOptions = CrFormatTypeOptions
      End With
      cryRpt.Export()
    Catch ex As Exception
      MsgBox(ex.ToString)
    End Try
    End Sub
End Class

NOTES:
cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

The Crystal Report is in your project location, there you can see CrystalReport1.rpt . So give the full path name of report here.

When you run this program you will get the Excel file (crystalExport.xls) in your computer's C:

madhavi replied to Aman Verma on 15-Dec-11 12:53 AM
Thanks for your reply.

but iam using vb6. not vb.net.

my vb6 code i put here for ur reference..

Option Explicit

Global hk As Appl
Global util As New clsUtil

Public oRpt As ADORBSReport_V1 'RBSReport Object
Public security As New clsEncryptDecrypt
Public cmpyname As String
Private frm As frmViewer
Private rs As ADODB.Recordset
Private Report As crFPXL0009r1
Private cmpyrs As ADODB.Recordset

Public Const scSystem = "TRUST OPERATION & MANAGEMENT SYSTEM"
Private Const scTitle = "LIST OF AGENT"

Public Sub StartProgram()
    'Call getPdate
    Call Proceed
End Sub
Public Sub ShowInq()
    Set oRpt = New ADORBSReport_V1
    oRpt.ReportFormCaption = "List of Agent - FPX0009"
  
    oRpt.Add "AAGTSydt", "Date (dd/mm/yyyy)", DateType, eADO, Trim(Date), , True, False, True, False, True, False
    oRpt.AddHelpKey "", "", DateType, False, True, , , True

    Set oRpt.db = cn
    DoEvents
   
    oRpt.Show
End Sub
Public Function PrintReport() As Boolean
    Screen.MousePointer = vbHourglass
    DoEvents

    Set Report = New crFPXL0009r1
    PrintReport = True
   
    If GetRecordSet = PrintReport Then
      Set frm = New frmViewer
     
      frm.crv.ReportSource = Report
      frm.crv.DisplayGroupTree = False
      frm.crv.DisplayToolbar = True
      frm.Caption = scTitle
      Report.txtTitle.SetText scTitle
      Call GetCmpyNameRecordSet
      Report.txtCmpy.SetText cmpyname
      Report.Database.SetDataSource rs
     
      Report.txtProgramID.SetText App.EXEName & "/ " & UserID & " "
      Report.txtSystemName.SetText scSystem
      Report.txtSelectionFrom.SetText "For Date: " & Format(oRpt.FromRange(1), "dd/MM/yyyy")

      Report.DisplayProgressDialog = True
      Report.EnableParameterPrompting = False
      Report.SelectPrinter Printer.DriverName, Printer.DeviceName, Printer.Port
      Report.PaperOrientation = crLandscape
      Report.PaperSize = crPaperA4
   Else
      MsgBox "Record not exist!", vbExclamation, App.Title
      PrintReport = False
      Screen.MousePointer = vbDefault
      DoEvents
    End If
    frm.Show
    frm.crv.ViewReport
End Function
Private Function GetRecordSet() As Boolean
    On Error GoTo ErrSave
   
    GetRecordSet = False
    Dim strsql$
    
    Screen.MousePointer = vbHourglass
    DoEvents
   
    strsql = " select AAgtBrn,AagtAgt,AAgtNm,AAgtId,AAgtSPAdr1,AAgtSPAdr2,AAgtSPAdr3,AAgtSPAdr4,AAgtSPPosCd,"
    strsql = strsql & " AAgtSPTown,USttDesc,UCtyDesc,uuhmtelh,aagtfi , AAgtFIAc, AAgtFIAcN"
    strsql = strsql & " From aagt inner join ustt on USttState = AAGTState inner "
    strsql = strsql & " join ucty on UCtyCntry = AAgtSPCntry inner join uuhm on uuhmid = aagtid"
    strsql = strsql & " where 1=1"
      
    Set rs = cn.Execute(strsql)
   
    GetRecordSet = (rs.RecordCount > 0)
    
    Exit Function
ErrSave:
    GetRecordSet = False
    MsgBox Err.Description
End Function
Public Function GetCmpyNameRecordSet()
    Dim sql
   
      sql = "SELECT UMgtMgtNm FROM UMgt where UMgtMgtCo ='01'"
     
      Set cmpyrs = cn.Execute(sql)
      If Not cmpyrs.EOF Then
      cmpyname = cmpyrs!UMgtMgtNm
      End If
     
End Function
Public Sub Proceed()
    Screen.MousePointer = vbHourglass
    DoEvents
   
    On Error Resume Next
      
    Call ShowInq
   
    Screen.MousePointer = vbHourglass
    DoEvents
   
    If oRpt.PrintAction <> icCancel Then
      Screen.MousePointer = vbHourglass
      Printer.Orientation = vbPRORPortrait
      Printer.PaperSize = vbPRPSA4
               
      If PrintReport Then
       
        Select Case oRpt.PrintAction
          Case icPrint:
            Report.PrintOut
            Unload frm
          Case icPreview:
            frm.Show
            DoEvents
            frm.crv.ViewReport
        End Select
       
      End If
    End If
   
    
    Screen.MousePointer = vbDefault
      
    Set oRpt = Nothing
    Set Report = Nothing

End Sub



above code is done using crystal report.and working good.
but they want to show th data in excel no need to show in crystal reports. so u have any idea please suggest me.b'coz iam new to vb6.

its very urgent.
thanks in advance..