Output a custom query to file

[Zaeed]

Hey, i'm trying to output a query i've built in code to a excel doc, but failing to get it to work..

Im trying to use the docmd.outputTo command.

I've tried creating a querydef, but can't seem to get it to work..

Does anyone know how I can do this?


Cheers

 

[pbaldy]

To output dynamic SQL to Excel I see two options. One is the QueryDef, either creating a new saved query or modifying the SQL of an existing one. The other would be to use automation to control Excel, and using the Excel method CopyFromRecordset to copy the data from a recordset based on the SQL. The first is probably simpler, so maybe you can post your effort to create the QueryDef and we can try to fix it.

 

[georgedwilkinson]

If you're going to Excel, try DoCmd.TransferSpreadsheet().

 

[Zaeed]

Ok, this is what i've got so far.. The error i'm getting is that the datatype is invalid.. So,i'm guessing that it doesn't like me passing in the query def as the output object.. I tried acStoredProcedure, but had the same result..

outputSQL = "SELECT tbl_Approval.ChangeNumber, qry_getName.Name, tbl_Change.Title, tbl_Change.Initiator, tbl_Change.Status, tbl_Change.Primary_Location, tbl_Approval.approved FROM (tbl_Approval INNER JOIN qry_getName ON tbl_Approval.approverID = qry_getName.UserId) INNER JOIN tbl_Change ON tbl_Approval.ChangeNumber = tbl_Change.ChangeNumber"
 
 
Set tempQRY = CurrentDb.CreateQueryDef("", outputSQL)
 
 
fileName2 = Format(DateSerial(Year(Date), Month(Date), 1), "mmmm") & " Remaining Approvers"
 
 
    DoCmd.OutputTo acOutputQuery, tempQRY, acFormatXLS, "P:\IMS\Change Management\Database\" & fileName2 & "2.xls"

 

[pbaldy]

I would create an actual named query, and use that name in the OutputTo line.

 

[Zaeed]

Do you mean in the query builder?

 

[jal]

Does the XLS file already exist? If so, you might have more luck if it doesn't yet exist - i should think the engine will create the file on the fly.

 

[Zaeed]

No it doesn't exist.. creating the file isn't the problem, its the command recognising the query

 

[jal]

Try this:

Dim qDEf As QueryDef
Set qDEf = CurrentDb.CreateQueryDef("myQuery1", "Select * from table1")
DoCmd.OutputTo acOutputQuery, "myQuery1", acFormatXLS, "C:\myQuery.xls"

 

[pbaldy]

Do you mean in the query builder?

No, I meant with this line:

Set tempQRY = CurrentDb.CreateQueryDef("", outputSQL)

as jal has demonstrated.

 

[Zaeed]

ah i follow now.. I thought the querydef WAS the query name.. which is probably why it wasn't working

 

[Zaeed]

Ok well that worked.. However when I ran the form for a second time it faulted and told me that the query already existed..
How do I delete the query once its been made?

 

[georgedwilkinson]

Something like:

CurrentDB.QueryDefs.Delete "myQuery1"

 

[Zaeed]

Cheers for that George.. Works like a dream now...

 

[georgedwilkinson]

Rock n' roll. Glad we could help.