CopyFromRecordset isn't bringing the data over?

[connie]

Have an unbound form (frmBuildCustomReport) with combo boxes whose row sources pull from a couple of tables. The form's command button then feeds the user's selection(s) into a query (qryBuildCustomReport) that ties it all together.

I've added two check boxes to this form. One check box outputs the query to a report. That works fine. The second checkbox, if selected, I would like it to copy the recordset of the qryBuildCustomReport and paste it into a new Excel workbook - an extra option if the user needs the info in Excel instead of the Access report.

Private Sub cmdOK_Click()
    'If View/Print Report box is checked, send it to an Access report'
    If Me!CheckViewPrintReport.Value = True Then
    DoCmd.OpenReport "rptCustomBuiltSheet", acViewPreview
    DoCmd.Close acForm, "frmBuildCustomReport"
    Exit Sub
    
    'If Export to Excel box is checked, export to a new Excel spreadsheet'
    ElseIf Me!CheckExportExcel.Value = True Then
    Dim dbs As DAO.Database
    
    Dim rstGetRecordSet As Recordset
    
    Dim objXL As Object
    Dim objCreateWkb As Object
    Dim objActiveWkb As Object
    
    Set dbs = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    Set objCreateWkb = objXL.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook
    
    objXL.Visible = True
    objActiveWkb.Sheets.Add
    objActiveWkb.Worksheets(1).Name = "Penetrations"
    
    Set rstGetRecordSet = dbs.OpenRecordset("qryBuildCustomReport")
    objActiveWkb.Worksheets("Penetrations").Cells(1, 1).CopyFromRecordset rstGetRecordSet
    
    Set objActiveWkb = Nothing
    Set objCreateWkb = Nothing
    Set objXL = Nothing
    rstGetRecordSet.Close
    dbs.Close
    Set rstGetRecordSet = Nothing
    Set dbs = Nothing
    
    Exit Sub
    
    'If neither box is checked, prompt user with message box'
    ElseIf Me!CheckViewPrintReport.Value = False And Me!CheckExportExcel.Value = False Then
    MsgBox "Please check either View/Print Standard Report or Export to Excel", vbOKOnly
    Me!CheckViewPrintReport.SetFocus
    Exit Sub
    Else
    End If
End Sub

Problem is something in my VBA...it creates a new Book1, correctly names the tab, but there's no data in it when it opens. It also creates Microsoft Visual Basic Run-time error 3061: "Too few poarameters. Expected 5."
There are 5 combo boxes on the form, so I'm assuming that's why it says that. However I have the query set up already to return all results for the combo boxes where nothing is selected.
Can anyone help me with this coding?

 

[DCrake]

'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Transfer the data to Excel
oSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
oBook.SaveAs "C:\Book1.xls"
oExcel.Quit

Thi sis how M$ does it see Article 922722

David

 

[connie]

Thi sis how M$ does it see Article 922722

David

Thanks! Actually I don't want to force a save on the user or quit Excel, though...the reason I'm doing it this way instead of DoCmd.Outputto is because I want it to open up in a new Excel spreadsheet on the screen, not save and not close Excel.

I did however try this but removed the last portion (with the Save & Quit Excel). I am getting the following error: "Run-time error '13': Type mismatch." It highlights the following line:

oSheet.Range("A1").CopyFromRecordset rs

Here is the portion of code I modified:

'If Export to Excel box is checked, export to a new Excel spreadsheet'
    ElseIf Me!CheckExportExcel.Value = True Then
    'Create a new workbook in Excel
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Add
    Set oSheet = oBook.Worksheets(1)
    
    'Transfer the data to Excel
    oSheet.Range("A1").CopyFromRecordset rs
    Exit Sub