Export reoprt to Excel

[gwunta1907]

Hi guys,

I have a report I would like to export to excel - well I want to actually be able to export the data that is in the report. After some searching on the forums I found what I thought was the solution but it throwing an error. The code is listed below

    Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    Dim rst As DAO.Recordset
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open("C:\temp\excelfile1.xls")
    Set xlWS = xlWB.Worksheets("Sheet1")
    Set rst = CurrentDb.OpenRecordset("qry_DailyTransReport")
    xlWS.Range("A1").CopyFromRecordset rst
    xlWB.Save
    
    rst.Close
    Set rst = Nothing

The error is getting picked up at the Set rst = CurrentDb.OpenRecordset("qry_DailyTransReport") line and the error message is "Too few parameters. Expected 1."

Any clues as to what I have done wrong here?

Thanks in advance

 

[Beetle]

Does the query have a defined parameter or does it reference a form control in the criteria for any of its columns?

 

[gwunta1907]

Thanks for getting back to me Beetle. Yes, the query uses criteria supplied by a control on a form. The form is open with the control containing the data for the query's criteria but still the error message persists

 

[Beetle]

When you open a parameter query like this through the Access user interface (i.e. you open it directly from the query objects list, or you open it from a form), Access provides an expression service that evaluates the form control and supplies the necessary value for the query parameter.

When you open a parameter query in code, you are bypassing the Access user interface and communicating directly with Jet/Ace (the database engine). Jet/Ace knows nothing about objects in the Access user interface (like forms and controls), so it cannot find the value for the parameter. To resolve this you need to open the query using the QueryDefs collection and supply the parameter value prior to opening the recordset. Code would look like the following (untested - new/modified lines in blue);

Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
    [COLOR="Blue"]Dim qdf As DAO.QueryDef[/COLOR]
    Dim rst As DAO.Recordset
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open("C:\temp\excelfile1.xls")
    Set xlWS = xlWB.Worksheets("Sheet1")
    [COLOR="blue"]Set qdf = CurrrentDb.QueryDefs("qry_DailyTransReport")
    qdf.Parameters(0) = Forms!YourFormName!YourControlName[/COLOR]
    [COLOR="blue"]Set rst = qdf.OpenRecordset[/COLOR]
    xlWS.Range("A1").CopyFromRecordset rst
    xlWB.Save
    
    rst.Close
    Set rst = Nothing