export query results to excel

[slaga9]

I am trying to move the results of a parameter query to a formatted excel file. I use 2 command buttons on a form, the first to run the query (works) the second to export the data to excel (driving me insane)

I am having problems having the information go to different work pages based on some criteria in the query. (specifically there are 5 possible sites in the access file, each entry has one, I want each site to have its own tab in the excel file)

I assume it is a syntax error of some sorts that I am getting.

If anyone here has time to look over it and notice what I am doing wrong It is greatly appreciated.
If I have posted this in the wrong area please let me know, and I apologize for the inconvenience.
thanks
Sean


This is my code:

Private Sub exportcmd_Click()
On Error GoTo err_Handler
   Const cTabTwo As Byte = 1
   ' Excel object variables
   
   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim prm As DAO.Parameter
   Dim rstOutput As DAO.Recordset
   Dim sOutput As String
   Dim sqlString As String
   Dim shtArray As Variant
   Dim siteArray As Variant
   Dim I As Integer
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("paraquery")
        For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    Set rstOutput = qdf.OpenRecordset
    
   DoCmd.Hourglass True
   
   ' set to break on all errors
   Application.SetOption "Error Trapping", 0

     ' start with a clean file built from the template file
   sOutput = CurrentProject.Path & "\salary recovery template.xls"
   
   ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
   Set appExcel = Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set wks = appExcel.Worksheets(cTabTwo)
   appExcel.Visible = True
   
   'Take the records in the current form and dump to Excel
   
    siteArray = Array("PickA", "PickB", "Darl", "Bruce", "Other", "Overhead")
   shtArray = Array("PickA549", "PickB349", "Darl348", "Bruce347", "Other", "Overhead")
   For I = 0 To 4
      sqlString = "SELECT * FROM paraquery WHERE jobsite='" & siteArray(I) & "'"
      Set rstOutput = CurrentDb.OpenRecordset(sqlString)
      Set wks = wbk.Worksheets.Add
      wks.Name = shtArray(I)
      wks.Range("A11").CopyFromRecordset rstOutput
   Next
  
exit_Here:
   ' Cleanup all objects  (resume next on errors)
   On Error Resume Next
   Set wks = Nothing
   'You may want to save the workbook here
   Set wbk = Nothing
   Set appExcel = Nothing
   rstOutput.Close
   Set rstOutput = Nothing
   DoCmd.Hourglass False
' Exit exportcmd_Click
   
err_Handler:
   ExportRequest = Err.Description
   ' Me.lblMsg.Caption = Err.Description
   'Resume exit_Here
End Sub

I am getting the error 3061- too few parameters, expected 6.

the debugger is highliteing this line

Set rstOutput = CurrentDb.OpenRecordset(sqlString)

my sqlsatement looks like this

SELECT [Employee_ID].[Recovery No], [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No], [Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[unknown], [Employee_ID].[Ref No], [Timesheettable1].[Employee], [Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]
WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*') And ([Timesheettable1].[JobNumbers]) Like '*' & [Forms]![srchfrm]![jobnumbersrchfrm] & '*' And ([Timesheettable1].[Employee]) Like '*' & [Forms]![srchfrm]![employee] & '*' And ([Timesheettable1].[PayPeriodEnd]) Like '*' & [Forms]![srchfrm]![payday] & '*' And ([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*';

 

[Ed2020]

The "too few parameters" error will be displayed whatever the syntax error is so can be a little misleading.

I'd guess that your syntax problem is the way you are passing criteria from form controls.

For example:

WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*'

should be:

"WHERE (([Employee_ID].[Recovery No]) Like " """" & "*" & [Forms]![srchfrm]![recovnumber] & "*" & """"

Please do not feed the trolls.....

 

[slaga9]

thanks for the response ED2020.
I have decided to take this project in a different route.

I have now given each site a separate query and separate export function.

The first site opens the excel template, and exports the data.
I want the 2-5 query's to export the data to the open excel files different tabs.

I know how to assign different worksheets. However, I need to learn how to send a query to an open excel file. Do you know what I mean?

**the path of the 2-5 Query's does not need to be specified...I just want it to go to an open excel file. (the first query will open the excel file)

any suggestions?
thanks
Sean

 

[JerryKlmns]

slaga9

.CopyFromRecordset says that you do know how to send a query to an open excel file. you were thinking something else?