Export USing VBA

[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

 

[Remou]

Yes, use TransferSpreadSheet. Have a look at this:

thread701-1380120

You need a similar idea because you can use an SQL string in the way you show above.

 

[GhostWolf]

Out of curiosity, have you tried to execute the statement without the quotation marks around Sql1? i.e. - DoCmd.OutputTo acOutputQuery, Sql1, acFormatXLS, "C:\1\test5.xls"

 

[Remou]

Oops, because you can't use an SQL string in the way you show above.