Using the Transfer Spreadsheet Command

[DanAuber]

Is it possible to use the transfer spreadsheet command by specifying the SQL to run rather than referring to a table or query ?

Thus I know that the code below will run query Q1 and ouput it:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Q1", "C:\temp\my_file.xls", True, ""

But If I have a string "Select * from [Table1]" can I insert that into the TransferSpreadsheet command somehow - if I try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Select * from [Table1]", "C:\temp\my_file.xls", True, ""

it falls over....

any help gratefully received

Dan

 

[lameid]

I don't think so. Obviously not if you tried it.

You could specify a query name and then write code to change the SQL property of the query.

Dim qry as DAO.Querydef

Set qry = Currentdb.Querydefs("qry name")
qry.SQL = "Select * from [Table1]"
Set qry = nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry Name", "C:\temp\my_file.xls", True, ""

 

[DanAuber]

When I run this I get an error on the Dim line: Compile error - User-Defined typed not defined ??

 

[JerryKlmns]

While in VBE goto Tools --> References scroll to find and check the reference of the DAO 3.6 library

 

[PWise]

try this

currentdb.execute INSERT INTO [Excel 8.0;imex=0;hder=yes;DATABASE=c:\filename.xls].[worksheetname$]
SELECT * from Tablename

 

[rfoye]

I've just been looking at the TransferSpreadsheet action, and what I've read says that you can not use a SQL string. You must first use the SQL to create a query object, then use that query object in your Transfer action.

-------------------
Rob Foye
Database Management
Regions Bank