CopyFromRecordset isn't bringing the data over? (1 Viewer)

connie · Jan 12, 2010

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.

Code:

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 · Jan 13, 2010

'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 · Jan 13, 2010

DCrake said:
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:

Code:

'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

CopyFromRecordset isn't bringing the data over? (1 Viewer)

connie

Registered User.

DCrake

Remembered

connie

Registered User.

Similar threads

Users who are viewing this thread