TransferSpreadsheet with query string

[smig]

I'm trying to use TransferSpreadsheet with query string, but I keep getting an error.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryString, strFileName

is it possible, or do TransferSpreadsheet require either a table or query grid to be used ?

 

[JANR]

or do TransferSpreadsheet require either a table or query grid to be used ?

Yes.

possible solution for you: http://www.access-programmers.co.uk/forums/showpost.php?p=1033786&postcount=5

JR

 

[smig]

thanks

I'm getting a type mismatch error on this line:
CurrentDb.QueryDefs.Append(qdf.Name)

 

[vbaInet]

*Create a query that you will use for all your exports so you don't have to create or delete a qdf. We will call this qryExports.

*Change the SQL of the qdf and export.

dim qdf as dao.querydef
 
set qdf = currentdb.querydefs("qryExports")
 
qdf.SQL = "SELECT ..."
 
docmd.transferspreadsheet ...

 

[JANR]

thanks

I'm getting a type mismatch error on this line:
CurrentDb.QueryDefs.Append(qdf.Name)

This line is not required since the CreateQueryDefs statement automatically appends a created query to the querydefs collection. (see access help)

It should have been:

Dim strSQL As String
Dim qdf As DAO.QueryDef
 
strSQL = "SELECT ......"
 
Set qdf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
docmd.TransferSpreadsheet....
CurrentDb.QueryDefs.Delete qdf.Name
 
qdf.Close

But I would use vbaInet's solution since this metode dosen't bloat the db by constantly create and delete objects.

JR

 

[smig]

thanks
vbaInet solution did the job.
I created an empty tempQuery grid to be used

@Janr
I looked in the help and saw that CreateQueryDefs statement automatically appends a created query to the querydefs collection.
already tried what you suggested but I couldn't make it to work.

as I do have something that work I see this topic as closed

 

[vbaInet]

Happy to hear smig