DoCmd.TransferSpreadsheet

[Guest]

I'm trying to transfer data from Access to Excel using
DoCmd.TransferSpreadsheet, I want to use a select query but it send an error
message, I can use already defined queries or table names, but for some
reason it won't let me use a sql select statement, eventhough it says I can
do so in the help file.

THIS DOESN'T WORK:
sSQL = "select * from qry_ANYQUERY"
DoCmd.TransferSpreadsheet acExport, , sSQL, sFile, True

THIS WORKS:
sSQL = "qry_ANYQUERY"
DoCmd.TransferSpreadsheet acExport, , sSQL, sFile, True

Can anybody help me?

 

[Ken Snell \(MVP\)]

You cannot use an SQL statement in TransferSpreadsheet. You must add the
query to QueryDefs collection and then export it. Here is some sample code:


EXAMPLE FOR HOW TO CREATE A NEW QUERY IN ORDER TO
EXPORT IT TO AN EXCEL FILE.
-------------------------

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String

Const strQName As String = "zExportQuery"

Set dbs = CurrentDb

strSQL = "SELECT * FROM Table_Name;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strQName, "C:\Ken.xls", False
dbs.QueryDefs.Delete strQName
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

 

[Junior728]

Hi,

I try to use your code below and it works. But i wish to include the
workbook to be save under a strQName and archived into a folder, the code
below is not doing it. (it just open up the directory for the user to save it
manually)

Any idea to help?

thanks.
=================
Option Compare Database

Sub Testing2()

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strSQL As String
Dim strQName As String

strQName = "Year 2007 FC Query by Cust, by CPN-Done by ML"
strSQL = "SELECT * FROM Table_Name;"

DoCmd.OpenQuery strQName
DoCmd.RunCommand acCmdExport

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQName)
qdf.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strQName, "C:\Meiling\MISC\MRPbyPN.xls", True -------> 'It is not
saving the wkbk data into the designated folder

MsgBox ("Mission Accomplished!")
dbs.QueryDefs.Delete strQName
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

End Sub