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


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
    End Sub

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

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

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
End Sub
Public Function PrintReport() As Boolean
    Screen.MousePointer = vbHourglass

    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
      MsgBox "Record not exist!", vbExclamation, App.Title
      PrintReport = False
      Screen.MousePointer = vbDefault
    End If
End Function
Private Function GetRecordSet() As Boolean
    On Error GoTo ErrSave
    GetRecordSet = False
    Dim strsql$
    Screen.MousePointer = vbHourglass
    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
    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
    On Error Resume Next
    Call ShowInq
    Screen.MousePointer = vbHourglass
    If oRpt.PrintAction <> icCancel Then
      Screen.MousePointer = vbHourglass
      Printer.Orientation = vbPRORPortrait
      Printer.PaperSize = vbPRPSA4
      If PrintReport Then
        Select Case oRpt.PrintAction
          Case icPrint:
            Unload frm
          Case icPreview:
        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..