Exporting to Excel CSV

[darinmc]

Hi
I would like to export a query to excel BUT I would like to do it in code using a command button.
I would like the format in an excel spreadsheet, so doing it using the transferSpreadsheet method might be better?

Private Sub cmdOutputTo_Click()
Dim Sql1 As String

Sql1 = "SELECT tblPayInv.EmpRegNo, tblEmployee.EmpNo, 1 AS Pay1, Sum(tblPayInv.BasicEmpHours) AS SumOfBasicEmpHours, tblEmployee.EmpNo, 2 AS Pay2, Sum(tblPayInv.OT1EmpHours) AS SumOfOT1EmpHours, tblEmployee.EmpNo, 3 AS Pay3, Sum(tblPayInv.OT2EmpHours) AS SumOfOT2EmpHours, tblEmployee.EmpNo, 7 AS Pay7, Sum(tblPayInv.HolHr) AS SumOfHolHr FROM tblEmployee INNER JOIN tblPayInv ON tblEmployee.EmpRegNo = tblPayInv.EmpRegNo GROUP BY tblPayInv.EmpRegNo, tblEmployee.EmpNo, 1, tblEmployee.EmpNo, 2, tblEmployee.EmpNo, 3, tblEmployee.EmpNo, 7, tblPayInv.WEdate HAVING (((tblPayInv.WEdate) = [W/end Date = d/m])) ORDER BY tblEmployee.EmpNo;"

DoCmd.OutputTo acOutputQuery, "Sql1", acFormatXLS, "C:\1\test5.xls"

End Sub

ERROR - cant find object Sql1

Please can you help, what am I leaving out?

Thx
Darin

 

[CautionMP]

darinmc,
[tt]OutputTo()[/tt] expects an object (table, query, form...) not a SQL string.

Try the same routine with [tt]TransferSpreadsheet()[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

Create a QueryDef with Sql1 and export this query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886