Output to Excel

[Spikemannen]

Hi

If I open a query in Access and uses the "Export to Excel" button everything works out just fine.
But when I want to make the export with a button using the code:
DoCmd.OutputTo acOutputQuery, "qryToExcel", acFormatXLS

I only get 255 chars from the PM-fields to excel.
Can anyone help me solve this problem?

I also want to make some filtering, with code string, to the query before I export it, could use some suggestions there also.

Thanks in advance

 

[Guus2005]

Don't know what PM-fields are but you can try the transferspreadsheet command

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Clients", "spreadsheet.xls"

 

[Spikemannen]

Thanks for your answer! It works... But how do I filter the query before I export it?

 

[Guus2005]

Use these functions to create an output query on the fly and export it.

Public Function MakeQueryDef(strSQLname As String, strSQLdef As String) As Boolean

   Dim qdf As QueryDef
   Dim dbs As Database
   
   On Error GoTo Err_MakeQueryDef
   
   Set dbs = CurrentDb
   
   DeleteQueryDef strSQLname ' Query delete
   
   Set qdf = dbs.CreateQueryDef(strSQLname, strSQLdef)
   
   MakeQueryDef = True
   
Exit_MakeQueryDef:
   Exit Function
   
Err_MakeQueryDef:
   MakeQueryDef = False
   MsgBox "Error in MakeQueryDef : " & Err.Number & ":" & Err.Description, vbExclamation, "Error making query definition."
   Resume Exit_MakeQueryDef
End Function

Public Sub DeleteQueryDef(strSQLname As String)

   Dim dbs As Database
   
   Set dbs = CurrentDb
   
   On Error Resume Next
   dbs.QueryDefs.Delete strSQLname 'Generates erorr when not exist.

End Sub

Public Function GetSqlDef(strQueryname As String) As String
'Return SQL definition of query
   GetSqlDef = CurrentDb.QueryDefs(strQueryname).SQL
End Function

 

[Spikemannen]

Thanks again for your answer.
But could you please define what you mean with strSQLname and strSQLdef?
Where do I put my sqlstring etc?

 

[Guus2005]

strSQLname expects the name of your new query.
strSQLDef is your SQL statement of the new query.
so strSQLDef is your sqlstring and you have to export strSQLname.

Hoi.