TransferSpreadsheet Export

[Jim]

I'm trying to use the TransferSpreadsheet statement below to export query
results to an excel file. Everything works fine if varQuery1 equals a stored
query, "qry_Facilities", but if I set varQuery1 to "Select FacilityID as
Facility Identification from tbl_Facility" I get a 3011 run-time error, could
not find object 'Select....'

DoCmd.TransferSpreadsheet acExport, , varQuery1, varDataOutputFolder &
varExcelFileName, , varTab1

What I have is a multi-language application and I'm trying to dynamically
change the column headings based on the language the User has selected. I
have a function that converts the English text to the User language but I
could not call it from within the query builder. I guess I could rewrite the
stored query with the appropriate column titles… is there a way to create a
stored query from vb?

Thanks in advance,

Jim

 

[Ken Snell \(MVP\)]

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

 

[Jim]

Ken,

Thanks... This gets me on the right path... Jim

 

[pietlinden]

I'm trying to use the TransferSpreadsheet statement below to export query
results to an excel file. Everything works fine if varQuery1 equals a stored
query, "qry_Facilities", but if I set varQuery1 to "Select FacilityID as
Facility Identification from tbl_Facility" I get a 3011 run-time error, could
not find object 'Select....'

DoCmd.TransferSpreadsheet acExport, , varQuery1, varDataOutputFolder &
varExcelFileName, , varTab1

What I have is a multi-language application and I'm trying to dynamically
change the column headings based on the language the User has selected. I
have a function that converts the English text to the User language but I
could not call it from within the query builder. I guess I could rewrite the
stored query with the appropriate column titles... is there a way to create a
stored query from vb?

Thanks in advance,

Jim

Two options:
1. save the query and delete it afterward you would have to append it
to the querydefs collection...
2. use the code here to send the open recordset to Excel.

http://www.mvps.org/access/modules/mdl0035.htm