Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting to Excel CSV

Status
Not open for further replies.

darinmc

Technical User
Feb 27, 2005
171
GB
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?

Code:
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
 
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)
 
Create a QueryDef with Sql1 and export this query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top