Need help in showing the report on Vb form

[Shilpa Jain]

Hi All,

I ahve to show the report on a vb form. I have written the code for exporting it to excel, but unable to get how to use it so that report is showed on vb form. I have an idea that I have to use datareport for this.
Can anyone help me in writing the code for the same??

Here is the code where I am exportingthe report ti Excel sheet:

Option Explicit
Dim sQueryName As String

Private Sub Form_Load()
 frmReports.WindowState = vbMaximized
End Sub

Private Sub GetRepCmd_Click()

'NID, RBAC and ISD status selected
If NIDChk.Value = 1 And RBACChk.Value = 1 And ISDChk.Value = 1 Then
sQueryName = "select Emp_No, Name, NID_Status, NID_Date, RBAC_Status, RBAC_Date, ISD_Status, ISD_Date from Access_Info where NID_Status = '" & NIDCmb.Text & "' and RBAC_Status = '" & RBACCmb.Text & "' and ISD_status ='" & ISDCmb.Text & "'"

'NID and RBAC status selected
ElseIf NIDChk.Value = 1 And RBACChk.Value = 1 Then
sQueryName = "select Emp_No, Name, NID_Status, NID_Date, RBAC_Status, RBAC_Date from Access_Info where NID_Status = '" & NIDCmb.Text & "' and RBAC_status = '" & RBACCmb.Text & "'"

'ISD status selected
Else: ISDChk.Value = 1
sQueryName = "select Emp_No, Name, ISD_Status, ISD_Date from Access_Info where ISD_Status = '" & ISDCmb.Text & "'"

End If

ExportToExcel

Unload Me

End Sub

Public Sub ExportToExcel()

'Const SaveAsFileName = "C:\Documents and Settings\n219649\Desktop\Report.xls"

'Variables for database connection
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim sConnString  As String

'Excel sheet variables
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xCount
Dim xCurrent
Dim xSheetNum

Set xlApp = CreateObject("Excel.Application")

xSheetNum = xlApp.SheetsInNewWorkbook
xSheetNum = 1
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet
xlApp.Visible = True
xlApp.DisplayAlerts = False

'Connecting to the Database
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tool\P_and_E\P_and_E.mdb"

conn.Open sConnString
Set cmd.ActiveConnection = conn

cmd.CommandText = sQueryName
cmd.CommandType = adCmdText
Set rs = cmd.Execute

If Not rs.BOF Then
'rs.MoveLast
rs.MoveFirst

'Title for Columns
For xCount = 1 To rs.Fields.Count
    xlSheet.Cells(1, xCount) = rs.Fields(xCount - 1).Name
Next

xCurrent = 1

Do Until rs.EOF
    xCurrent = xCurrent + 1
    For xCount = 1 To rs.Fields.Count
        xlSheet.Cells(xCurrent, xCount) = rs.Fields(xCount - 1).Value
    Next
    rs.MoveNext
Loop
End If
'xlBook.SaveAs SaveAsFileName
rs.Close

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

Set rs = Nothing
Set cmd = Nothing

'Close connection to database
conn.Close
Set conn = Nothing

End Sub