export dynamic query to excel spreadsheet (1 Viewer)

[selahlynch]

Hello,

I'm trying to export a crosstab query from a form in Access to an Excel spreadsheet.

Unfortunately the following code did not work:

sqlquery = "SELECT * FROM AllPossibilitiesAttendance WHERE isSS"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, sqlquery, "C:\AllPossibilitiesAttendance.xls", True

It gave me an error "Run-time error '3011: The microsoft engine could not find the object 'SELECT * FROM AllPossibilitiesAttendance WHERE isSS; '..."

I'd like to keep my query as a variable within the code, because the query is dynamic; it is modified as a result of selections that are made on the form.

Any suggestions?

 

[namliam]

In order to export a query you need to have it either as a report, form or query object

You can then use the OutputTo or Transferspreadsheet commands to export it...

Good luck

 

[selahlynch]

Perhaps you can help me with creating a query object within VBA code?

The following code gave me an error:

Dim dbsA As Database
Set dbsA = CurrentDb()
Dim qdfTemp As QueryDef
Set qdfTemp = dsbA.CreateQueryDef("tempqueryy", "SELECT * FROM Employees")

The error was "Run-time error '424': Object required"

 

[namliam]

The easiest way to do this is have a "default" export query... Just an empty "dummy" query object that is called like "qryExport" or something

Then filling that object with sql works like so:

Dim qryDef as queryDef
set qryDef = currentdb.querydefs("qryExport")
qrydef.sql = "Select ....."
set qrydef = nothing
' then export qryExport

Hope that helps

 

[selahlynch]

Thanks, that did help.