DoCmd.OutputTo with variable

[esengul]

i have a form with checkbox, combobox and 2 textboxes. i am trying to export my query results based on the form's control into excel. here is my code
im frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String
Dim rs As Recordset
Dim qdf As QueryDef
Dim sql As String
Dim filename As String

filename = GetSaveFile
If Len(filename) < 5 Then
Exit Sub
Else
If Me.ChkAll = True Then
sql = "select * from QuarterlyDup"

ElseIf Not IsNull(qrt) Then
sql = "select * from QuarterlyDup WHERE ((([QuarterlyDup].[quarter])= '" & Me.cmbQrt.Value & "'));"

Else
sql = " select * from QuarterlyDup where (THgAnalyticalRuns.AnalysisDate) Between " & Me.SDate.Value & " And " & Me.Edate.Value
End If

Set rs = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs, filename

End If
Let me know what i missing here,
Thanks in advance

 

[PHV]

Place the cursor in your code inside the TransferSpreadsheet word and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

The TransferSpreadsheet action relies on a TableDef or QueryDef object in the database window. You can repair your code rather easily by creating an empty QueryDef using the query editor, and saving it as "qryExport" or similar. Then, add the SQL from your routine directly to the QueryDef object at runtime:

Private Sub cmdExport_Click()
On Error GoTo ErrHandler
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim sql As String
  Dim filename As String
  
  filename = GetSaveFile()

  If Len(filename) < 5 Then Exit Sub

  If Me.ChkAll = True Then
    sql = "select * from QuarterlyDup"
  ElseIf Not IsNull(qrt) Then
    sql = "select * from QuarterlyDup  WHERE " & _
          "((([QuarterlyDup].[quarter])= '" & Me.cmbQrt.Value & "'));"
  Else
    sql = " select * from QuarterlyDup where (THgAnalyticalRuns.AnalysisDate) " & _
          "Between #" & Me.SDate.Value & "# And #" & Me.EDATE.Value & "#"
  End If
     
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("qryExport")
  qdf.SQL = sql
        
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, filename

ExitHere:
  On Error Resume Next
  Set db = Nothing
  Set qdf = Nothing
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[esengul]

Thanks alot. it works perfectly
Thanks again