export query to excel and save

[Shift838]

I want to export a query to excel and save to a location on the hard drive that I am running via a command button.

I have a preview that runs with no problems as follows, can this code be modified to automaticallly export it with little effort?

Private Sub cmdpreport_Click()
On Error GoTo Err_Cmdpreport_Click

Dim stDocName, strfilter As String

stDocName = "NSAPIDMM"


strfilter = "txtidowner ='" & Me.cmbowners.Value & "'"

DoCmd.OpenReport stDocName, acPreview, , strfilter

Exit_Cmdpreport_Click:
Exit Sub

Err_Cmdpreport_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Cmdpreport_Click
End Sub

 

[Remou]

OutputTo or TransferSpreadsheet may suit, depending on whether it is a report or query that you wish to save.

 

[Shift838]

it's a query, but I have to run the query based on the strfilter above..

 

[Remou]

A few thoughts:

Dim strFilter
Dim qdf As DAO.QueryDef

strfilter = "txtidowner ='" & Me.cmbowners.Value & "'"

If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='qryQuery'")) Then
    Set qdf = CurrentDb.CreateQueryDef("qryQuery", "Select * From tblTable Where " & strFilter)
Else
   Set qdf = CurrentDb.QueryDefs("qryQuery")
   qdf.SQL = "Select * From tblTable Where " & strFilter
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, qdf.Name & Format(Date, "yymmdd") & ".xls"

 

[Shift838]

Remou,

That is exactly what I was looking for! (well close enough) I was able to modify it abit to hit a query instead and it works great!